Data Need : Supplier Number , Supplier Site , Supplier Active Pay site , Payment Term, Payment Method , Business unit, currency.Theseall data we need to get from ERP based on the input as PO Number and a dff Attribute on the Supplier site. Currently we have a BI report which fetch all this data when provided the PO Number and other input fields.Question :- Looking for Rest API based approach to achieve the same.Input Combinations for API we can Provide-Rest API should be able to get all these fields based on PO Number.Rest API should be able to get all these fields based on Distribution Concatenated Code,Supplier Site ID(DFF attribute) .Rest API should be able to get all these fields based on Distribution Concatenated Code, Supplier Number, Supplier site .These output data should be correct and follow some validations (for example: supplier site and currency must be active, supplier site must be pay site , BU must be billtobu, etc)
Code Snippet (add any code snippets that support your topic, if applicable):
SELECT
bu.bu_name AS "BUSINESS UNIT",
pll.base_uom AS uom,
pll.item_description AS description,
pll.line_num AS po_line_number,
ph.segment1 AS po_number,
nvl((
SELECT
currency_code
FROM
fnd_currencies_tl
WHERE
currency_code = ph.currency_code
AND language = 'US'
), 'N') AS "Invoice Currency",
nvl((
SELECT
fnb.enabled_flag
FROM
fnd_currencies_b fnb, fnd_currencies_tl fnt
WHERE
1 = 1
AND fnt.currency_code = ph.currency_code
AND fnt.language = 'US'
AND fnt.currency_code = fnb.currency_code
), 'N') AS "currency_active_flag",
pos.vendor_name AS "SUPPLIER_NAME",
ph.vendor_id AS "SUPPLIER_ID",
plla.product_fisc_classification,
ppa.segment1 AS project_number,
ptv.task_number AS task_number,
pet.expenditure_type_name AS expenditure_type,
hru.name AS expenditure_organisation,
pos.segment1 AS "SUPPLIER_NUMBER",
vs.vendor_site_code AS "Supplier Site",
apt.name AS "Payment Terms",
to_char(sysdate, 'YYYY/MM/DD') AS "Invoice Received Date",
to_char(sysdate, 'YYYY/MM/DD') AS "Accounting Date",
ibeppm.payment_method_code AS "Payment Method",
ship_to.location_code AS ship_to_location_code,
ship_to1.location_code AS ship_from_location_code,
CASE
WHEN vs.effective_end_date >= sysdate THEN
'Y'
ELSE
'N'
END AS "Supp Site Status",
rsh.receipt_num AS receipt_number,
rsh.packing_slip AS packing_slip,
gcc.concatenated_segments distribution_combination,
/* (SELECT CONCATENATED_SEGMENTS
FROM Gl_CODE_COMBINATIONS gcc,
PO_DISTRIBUTIONS_ALL pda
WHERE 1 = 1
and pda.po_line_id = pll.po_line_id
and gcc.CODE_COMBINATION_ID = pda.CODE_COMBINATION_ID
and rownum = 1) distribution_combination */
vs.ATTRIBUTE5 AS "SUPPLIER_SITE_ID",
bu.primary_ledger_id AS "LEDGER_ID"
FROM
po_lines_all pll,
po_headers_all ph,
po_line_locations_all plla,
po_distributions_all pda,
hr_all_organization_units hou,
poz_suppliers_v pos,
poz_supplier_sites_all_m vs,
fun_all_business_units_v bu,
--fun_all_business_units_v buss,
ap_terms_tl apt,
iby_ext_party_pmt_mthds ibeppm,
iby_external_payees_all ibepa,
hr_locations_all ship_to,
hr_locations_all ship_to1,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
gl_code_combinations gcc,
pjf_projects_all_vl ppa,
pjf_tasks_v ptv,
hr_organization_units hru,
--PJF_PROJECT_TYPES_TL PPT,
pjc_transaction_controls ptc,
pjf_exp_types_tl pet
WHERE
ph.po_header_id = pll.po_header_id
AND pll.po_line_id = plla.po_line_id
AND pll.po_line_id = pda.po_line_id
AND plla.line_location_id (+) = pda.line_location_id
AND ph.prc_bu_id (+) = hou.organization_id
AND ph.vendor_id = pos.vendor_id
AND pos.vendor_id = vs.vendor_id (+)-- AND ph.vendor_site_id(+) = vs.vendor_site_id
AND ph.req_bu_id (+) = bu.bu_id
--AND vs.prc_bu_id (+)= buss.bu_id
AND ph.terms_id (+) = apt.term_id
AND ibepa.supplier_site_id (+) = vs.vendor_site_id
AND ibeppm.ext_pmt_party_id (+) = ibepa.ext_payee_id
AND ship_to.location_id(+)= ph.ship_to_location_id
AND ship_to1.location_id(+) = ph.bill_to_location_id
AND rsh.shipment_header_id (+) = rsl.shipment_header_id
AND rsl.po_line_id (+) = plla.po_line_id
AND plla.po_header_id = rsl.po_header_id (+)
AND rsl.po_line_location_id (+) = plla.line_location_id
AND pda.pjc_project_id = ppa.project_id (+)
AND pda.pjc_task_id = ptv.task_id (+)
AND ppa.project_id (+) = ptv.project_id
AND ppa.carrying_out_organization_id = hru.organization_id (+)
AND pda.pjc_expenditure_type_id = pet.expenditure_type_id (+)
AND pda.accrual_account_id = gcc.code_combination_id
-- check below joins
--AND PPT.PROJECT_TYPE_ID=PPA.PROJECT_TYPE_ID
AND ppa.project_id = ptc.project_id (+)
AND ptc.task_id (+) = ptv.task_id
AND pet.expenditure_type_id = ptc.expenditure_type_id (+)
AND vs.pay_site_flag = 'Y'
AND ibeppm.primary_flag = 'Y'
AND apt.language = userenv('lang')
AND vs.inactive_date IS NULL
AND ph.segment1 IN ( :p_po_number )
UNION
SELECT
flv.tag AS "BUSINESS UNIT",
NULL uom,
NULL description,
NULL po_line_number,
NULL po_number,
nvl((
SELECT
currency_code
FROM
fnd_currencies_tl
WHERE
currency_code = gl.CURRENCY_CODE
AND language = 'US'
), 'N') AS "Invoice Currency",
nvl((
SELECT
fnb.enabled_flag
FROM
fnd_currencies_b fnb, fnd_currencies_tl fnt
WHERE
1 = 1
AND fnt.currency_code = gl.CURRENCY_CODE
AND fnt.language = 'US'
AND fnt.currency_code = fnb.currency_code
), 'N') AS "currency_active_flag",
psv.vendor_name AS "SUPPLIER_NAME",
NULL SUPPLIER_ID,
NULL product_fisc_classification,
NULL project_number,
NULL task_number,
NULL expenditure_type,
NULL expenditure_organisation,
psv.segment1 AS "SUPPLIER_NUMBER",
pssam.vendor_site_code AS "Supplier Site",
att.name AS "Payment Terms",
to_char(sysdate, 'YYYY/MM/DD') AS "Invoice Received Date",
to_char(sysdate, 'YYYY/MM/DD') AS "Accounting Date",
ibeppm.payment_method_code AS "Payment Method",
NULL ship_to_location_code,
NULL ship_from_location_code,
CASE
WHEN pssam.effective_end_date >= sysdate THEN
'Y'
ELSE
'N'
END AS "Supp Site Status",
NULL receipt_number,
NULL packing_slip,
gcc.concatenated_segments distribution_combination,
/* (SELECT CONCATENATED_SEGMENTS
FROM Gl_CODE_COMBINATIONS gcc,
PO_DISTRIBUTIONS_ALL pda
WHERE 1 = 1
and pda.po_line_id = pll.po_line_id
and gcc.CODE_COMBINATION_ID = pda.CODE_COMBINATION_ID
and rownum = 1) distribution_combination */
pssam.ATTRIBUTE5 AS "SUPPLIER_SITE_ID",
fabu.primary_ledger_id AS "LEDGER_ID"
FROM
FND_LOOKUP_VALUES flv,
gl_code_combinations gcc,
ap_terms_tl att,
poz_suppliers_v psv,
poz_supplier_sites_all_m pssam,
iby_ext_party_pmt_mthds ibeppm,
iby_external_payees_all ibepa,
FUN_ALL_BUSINESS_UNITS_V fabu,
GL_LEDGER_LE_V gl
WHERE
1 = 1
AND flv.LOOKUP_TYPE = 'BU_LE_IDENTIFIER'
AND flv.LOOKUP_CODE = gcc.segment1
AND psv.vendor_id = pssam.vendor_id
AND pssam.vendor_site_id = ibepa.supplier_site_id
AND pssam.TERMS_ID = att.TERM_ID
AND ibepa.ext_payee_id = ibeppm.ext_pmt_party_id
AND pssam.pay_site_flag = 'Y'
AND ibeppm.primary_flag = 'Y'
and flv.LANGUAGE ='US'
and fabu.BU_NAME = flv.tag
AND att.language = userenv('lang')
and gl.ledger_id = fabu.PRIMARY_LEDGER_ID
and flv.description = gl.legal_entity_name
AND gcc.concatenated_segments in (:p_concatenated_segments)
AND ( coalesce(NULL, :p_supplier_site) IS NULL OR ( pssam.vendor_site_code IN ( :p_supplier_site ) ) )
AND ( coalesce(NULL, :p_supplier_number) IS NULL OR ( psv.segment1 IN ( :p_supplier_number ) ) )
AND ( coalesce(NULL, :p_supplier_site_id) IS NULL OR ( pssam.ATTRIBUTE5 IN ( :p_supplier_site_id ) ) )
-- _**End of Report**_
We are using this code now , need rest api's to get all this data for these combinations of inputs