SELECT DISTINCT pha.segment1 AS po_number
,TO_CHAR(pha.CREATION_DATE,'YYYYMMDD') AS po_creation_date
,pla.line_num AS po_line_number
,apa.INVOICE_NUM
,apa.INVOICE_AMOUNT
,apa.INVOICE_TYPE_LOOKUP_CODE
,apa.PAYMENT_STATUS_FLAG
,apa.PAYMENT_CURRENCY_CODE
,apa.DISCOUNT_AMOUNT_TAKEN
,apa.TOTAL_TAX_AMOUNT
,apa.AMOUNT_PAID
,TO_CHAR(apa.INVOICE_DATE,'YYYYMMDD') INVOICE_CREATION_DATE
,apla.LINE_NUMBER
,apla.QUANTITY_INVOICED
,apla.UNIT_PRICE
,apla.UNIT_MEAS_LOOKUP_CODE
,(apla.QUANTITY_INVOICED *apla.UNIT_PRICE) as invoice_line_amount
,psv.vendor_name
,psv.vendor_id
,pha.vendor_site_id
,esi.item_number
,esi.description
,esi.inventory_item_id
,(SELECT hp.party_id
FROM egp_trading_partner_items a
,hz_parties hp
,egp_item_relationships_b b
WHERE 1=1
AND a.tp_type = 'MANUFACTURER'
AND hp.party_id = a.trading_partner_id
AND a.tp_item_id = b.tp_item_id
AND b.item_relationship_type = 'MFG_PART_NUM'
AND b.inventory_item_id = esi.inventory_item_id) AS manufacturer_id
,(SELECT hp.party_name
FROM egp_trading_partner_items a
,hz_parties hp
,egp_item_relationships_b b
WHERE 1=1
AND a.tp_type = 'MANUFACTURER'
AND hp.party_id = a.trading_partner_id
AND a.tp_item_id = b.tp_item_id
AND b.item_relationship_type = 'MFG_PART_NUM'
AND b.inventory_item_id = esi.inventory_item_id) AS manufacturer_name
,(SELECT a.tp_item_number
FROM egp_trading_partner_items a
,hz_parties hp
,egp_item_relationships_b b
WHERE 1=1
AND a.tp_type = 'MANUFACTURER'
AND hp.party_id = a.trading_partner_id
AND a.tp_item_id = b.tp_item_id
AND b.item_relationship_type = 'MFG_PART_NUM'
AND b.inventory_item_id = esi.inventory_item_id) AS manfr_part_num
,(SELECT a.TP_ITEM_DESC
FROM egp_trading_partner_items a
,hz_parties hp
,egp_item_relationships_b b
WHERE 1=1
AND a.tp_type = 'MANUFACTURER'
AND hp.party_id = a.trading_partner_id
AND a.tp_item_id = b.tp_item_id
AND b.item_relationship_type = 'MFG_PART_NUM'
AND b.inventory_item_id = esi.inventory_item_id) AS MANFR_PART_DESC
,( select TO_CHAR(aipa.creation_date,'YYYYMMDD')
from AP_INVOICE_PAYMENTS_ALL aipa
where apla.invoice_id = aipa.invoice_id
)payment_date
,(SELECT name from AP_TERMS_TL att where att.term_id =apa.terms_id and att.LANGUAGE ='US') as payment_term
--,NULL AS Company
,(
SELECT pha.segment1
FROM po_headers_all poh
,po_lookup_codes plc
WHERE 1=1
AND plc.lookup_type = 'AGREEMENT_TYPE'
AND poh.type_lookup_code = 'BLANKET'
--AND NVL(poh.closed_code,'OPEN') = 'OPEN'
AND poh.APPROVED_FLAG = 'Y'
AND poh.type_lookup_code = plc.lookup_code
AND poh.po_header_id = pha.po_header_id
) AS blankatDescription
,psv.vendor_id AS Supplier_Configuration_ID
,esi.item_number AS SUPP_ITEM_NUM
--,NULL AS description_bpa
,pla.item_description SUPP_ITEM_DESC
,(apa.INVOICE_AMOUNT+apa.TOTAL_TAX_AMOUNT) AS net_invoice_amount
,NULL AS PACKAGING_STRING
,NULL AS Invoice_Line_Charge_Amount
,(SELECT DISTINCT papf.full_name
FROM po_agent_accesses paa
,per_person_names_f_v papf
WHERE 1=1
AND paa.agent_id = papf.person_id
AND papf.name_type = 'GLOBAL'
AND pha.agent_id = paa.agent_id) AS Buyer
,NULL AS Master_Contract_Number
,NULL AS Document_Type
--,apla.ACCOUNTING_DATE as GL_ACCOUNTING_DATE
,TO_CHAR(apla.ACCOUNTING_DATE,'YYYYMMDD') as GL_ACCOUNTING_DATE
,REGEXP_REPLACE(gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 1, gcc.segment1), '(,)', '') as company_desc_new
,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 1, gcc.segment1) company_desc
,gcc.segment2 cost_center
,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) cost_center_desc
,gcc.segment3 department_number
,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 3, gcc.segment3) department_name
,gcc.segment4 Account
,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.segment4) Account_name
,NULL AS MANFR_BRAND_NAME
,psv.vendor_name AS SUPPLIER_NAME
,NULL AS INVOICE_DUE_DATE
,TO_CHAR(apa.invoice_received_date,'YYYYMMDD')AS INVOICE_RECEIPT_DATE
,apa.freight_amount AS TOTAL_FREIGHT_AMT
,apa.approved_amount AS TOTAL_SPL_CHARGE_AMT
,NULL AS INV_SUB_LINE_NUM
,apa.approval_status AS LINE_STATUS
,apla.QUANTITY_INVOICED AS LINE_QTY_EACH
,apla.included_tax_amount AS INV_LINE_TAX_AMT
,apla.amount AS INV_LINE_PAID_AMT
,esi.fixed_lot_multiplier AS PROD_LOT_NUM
,NULL AS REMIT_TO_ID
,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 4, gcc.segment4) AS ACCOUNT_DESC
,NULL AS SUB_ACCOUNT_NUM
,NULL AS SUB_ACCOUNT_NAME
,NULL AS EXT_TRACKING_NUM
,(SELECT ieb.bank_account_num
FROM
poz_suppliers ps,
poz_supplier_sites_all_m pssm,
iby_external_payees_all payee,
iby_pmt_instr_uses_all uses,
iby_ext_bank_accounts ieb,
hz_parties bank,
hz_parties hzp
WHERE
ps.vendor_id = pssm.vendor_id
AND ps.party_id = payee.payee_party_id
AND payee.supplier_site_id = pssm.vendor_site_id
AND uses.instrument_type = 'BANKACCOUNT'
AND payee.ext_payee_id = uses.ext_pmt_party_id
AND uses.payment_function = 'PAYABLES_DISB'
AND uses.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = bank.party_id(+)
AND hzp.party_id = ps.party_id
AND ps.vendor_id = psv.vendor_id
AND pssm.vendor_site_id = pha.vendor_site_id) as SUPPLIER_BANK_ACC
FROM po_headers_all pha
,po_lines_all pla
,ap_invoices_all apa
,ap_invoice_lines_all apla
,egp_system_items_v esi
,po_line_locations_all line_loc
,ap_invoice_distributions_all aida
,poz_suppliers_v psv
,gl_code_combinations gcc
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = apa.po_header_id
AND apa.invoice_id = apla.invoice_id
AND pla.po_line_id = line_loc.po_line_id
AND pla.item_id = esi.inventory_item_id
AND line_loc.ship_to_organization_id = esi.organization_id
AND apla.invoice_id = aida.invoice_id(+)
and apla.line_number = aida.invoice_line_number(+) --added
AND psv.vendor_id = pha.vendor_id
AND aida.dist_code_combination_id = gcc.code_combination_id
AND pha.last_update_date >= (to_char(to_date(:p_last_run_date,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'))
--AND pha.segment1 ='KSM1001'
No comments:
Post a Comment