AP Credit Card Transactions Report
SELECT *
FROM (
SELECT DISTINCT per.first_name,
per.last_name,
hr.name org_name,
t.credit_card_trxn_id, --replaced column by t.trx_id,
t.validate_code,
t.card_program_id,
NULL expensed_amount, --t.expensed_amount, ??
icc.masked_cc_number card_number,
t.reference_number,
t.transaction_type,
To_char(t.transaction_date,'DD-MON-YYYY') transaction_date,
t.transaction_amount,
t.debit_flag,
To_char(t.billed_date,'DD-MON-YYYY') billed_date,
t.billed_amount,
t.billed_decimal,
t.billed_currency_code,
To_char(t.posted_date,'DD-MON-YYYY') posted_date,
t.posted_amount,
t.posted_decimal,
t.posted_currency_code,
t.currency_conversion_rate,
t.mis_industry_code,
t.merchant_tax_id,
t.merchant_reference,
t.merchant_name1,
t.merchant_address1,
t.merchant_city,
t.merchant_province_state,
t.merchant_postal_code,
t.merchant_country,
t.total_tax,
t.local_tax,
t.national_tax,
t.other_tax,
hr.organization_id, --replaced by t.org_id
To_char(t.last_update_date,'DD-MON-YYYY') last_update_date,
t.last_updated_by,
t.last_update_login,
To_char(t.creation_date,'DD-MON-YYYY') creation_date,
t.created_by,
t.folio_type,
To_char(t.car_rental_date,'DD-MON-YYYY') car_rental_date,
To_char(t.car_return_date,'DD-MON-YYYY') car_return_date,
t.car_rental_location,
t.car_return_location,
t.car_return_state,
t.car_renter_name,
t.car_rental_days,
t.car_rental_agreement_number,
t.car_class,
t.car_total_mileage,
t.car_daily_rate,
To_char(t.hotel_arrival_date,'DD-MON-YYYY') hotel_arrival_date,
To_char(t.hotel_depart_date,'DD-MON-YYYY') hotel_depart_date,
t.hotel_stay_duration,
t.hotel_room_rate,
t.hotel_no_show_flag,
t.hotel_city,
t.hotel_state,
t.hotel_folio_number,
t.hotel_room_type,
To_char(t.air_departure_date,'DD-MON-YYYY') air_departure_date,
t.air_departure_city,
t.air_routing,
t.air_arrival_city,
t.air_stopover_flag,
t.air_base_fare_amount,
t.air_carrier_abbreviation,
t.air_passenger_name,
t.air_refund_ticket_number,
t.air_exchanged_ticket_number,
t.air_agency_number,
t.air_ticket_number,
t.payment_flag,
t.record_type,
t.merchant_activity,
t.financial_category, --replaced by t.category,
NULL report_header_id, --t.report_header_id, ??
NULL expense_status, --t.expense_status, ?
NULL company_prepaid_invoice_id,--t.company_prepaid_invoice_id, ??
NULL inactive_emp_wf_item_key, --t.inactive_emp_wf_item_key,
ee.location_id , --replaced by t.location_id, ??
t.request_id,
NULL merchant_country_code,--t.merchant_country_code,??
To_char(t.dispute_date,'DD-MON-YYYY') dispute_date,
t.payment_due_from_code,
To_char(t.trxn_available_date,'DD-MON-YYYY') trxn_available_date, --Replaced by trx_available_date
t.card_acceptor_id,
t.trxn_detail_flag,
t.card_id,
t.description,
t.company_number,
t.market_code,
NULL validate_request_id, --t.validate_request_id,
NULL customer_code, --t.customer_code,
NULL purchase_identification,--t.purchase_identification,
ee.fuel_type, --replaced by t.fuel_type,
NULL fuel_uom_code, --t.fuel_uom_code,
NULL fuel_quantity, --t.fuel_quantity,
NULL fuel_unit_price, --t.fuel_unit_price,
NULL fuel_sale_amount, --t.fuel_sale_amount
t.air_ticket_issuer, --replaced by t.air_agency_name,
NULL air_total_legs, -- t.air_total_legs,
NULL customer_data1, -- t.customer_data1
per.display_name,
gcc.segment8,
gcc.segment7,
gcc.segment6,
gcc.segment5,
gcc.segment4,
gcc.segment3,
gcc.segment1,
paa.default_code_comb_id,
paam.assignment_type
FROM exm_credit_card_trxns t, -- replaced by ap_credit_card_trxns_all
exm_cards d, -- replaced by ap_cards_all
iby_creditcard icc,
per_person_names_f per, -- added to fetch first_name and last_name
per_all_people_f papf,
per_all_assignments_f paa,
gl_code_combinations gcc,
per_all_assignments_m paam,--added newly to connect with hr_all_organization_units
hr_all_organization_units hr,
exm_expenses ee
WHERE 1=1
AND d.card_id = t.card_id (+)
AND icc.instrid = d.card_reference_id
AND icc.card_issuer_code = 'MASTERCARD'
AND d.person_id = per.person_id
AND per.person_id =paa.person_id
AND paa.default_code_comb_id IS NOT NULL
AND papf.person_id =paa.person_id
AND paa.default_code_comb_id =gcc.code_combination_id
AND paam.assignment_type = 'E'
AND paam.primary_assignment_flag = 'Y'
AND paam.effective_latest_change = 'Y'
AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND hr.organization_id = paam.organization_id
AND per.person_id =paam.person_id
AND d.card_id = ee.card_id (+)
AND t.transaction_date BETWEEN paa.effective_start_date AND Nvl(paa.effective_end_date,SYSDATE)
AND SYSDATE BETWEEN per.effective_start_date AND Nvl(per.effective_end_date,SYSDATE)
--AND upper(per.attribute1) = upper(NVL(:p_user_id,per.attribute1) ) /* 19-May-25 Comment this Parameters*/
--AND hr.organization_id = NVL(:p_org_id,hr.organization_id)
--AND (hr.organization_id IN (:p_org_id) OR COALESCE(:p_org_id,null) IS Null)
AND Substr(hr.name, 1, 3)=Nvl(Substr(:p_org_id, 1, 3),Substr(hr.name, 1, 3))
-- --AND t.transaction_date BETWEEN TO_DATE(:p_date_from,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE(NVL(:p_date_to,SYSDATE),'YYYY/MM/DD HH24:MI:SS')
AND t.transaction_date BETWEEN Nvl(:p_date_from,t.transaction_date) AND Nvl(:p_date_to,SYSDATE)
AND per.display_name = Nvl(:P_PERSON_ID,per.display_name) )
WHERE 1=1
AND Substr(org_name,5,4) =Nvl(:P_DEPT_NO,Substr(org_name,5,4))
--AND gcc.segment5 = NVL(:P_DEPT_NO,gcc.segment5)
----AP E-Invoice DMAP E-Invoice Report
select DISTINCT 1 AS KEY ,
--aps.vendor_name SUPPLIER_NAME,
supplierpartyname.party_name supplier_name ,
'EI00000000030' supplier_tin ,
'BRN: NA' supplier_registration_number ,
'NA' seller_sst_reg_number ,
'NA' supplier_tourism_tax_registration_number ,
'' supplier_email ,
'26101' supplier_msic_code ,
'Manufacture of diodes, transistors and similar semiconductor devices' supplier_business_act_description ,
supplierlocation.address1 supplier_address_line1 ,
supplierlocation.address2 supplier_address_line2 ,
supplierlocation.address3 supplier_address_line3 ,
supplierlocation.postal_code AS zip ,
nvl(supplierlocation.city, 'NA') supplier_city ,
'17' supplier_state ,
ftv.iso_territory_code supplier_country ,
nvl(ass.phone, '+6066822001') supplier_contact_number ,
'ON Semiconductor Malaysia Sdn. Bhd.' buyer_name , -- hl.DESCRIPTION
'C898164070' buyer_tin , --xep.LEGAL_ENTITY_IDENTIFIER BUYER_TIN,
'BRN: 197901004164' buyer_reg_number ,--xr.REGISTRATION_NUMBER BUYER_REG_NUMBER,
'N101808320000468' buyer_sst_reg_number ,
'MYSE1-FIN-E-Invoicing@onsemi.com' buyer_email ,
hl.address1 buyer_address_line_1 ,
hl.address2 buyer_address_line_2 ,-- no address line2 in system
hl.address3 buyer_address_line_3 ,-- no address line3 in system
hl.postal_code buyer_zip ,
hl.city buyer_city ,
'05' buyer_state ,-- no state in system
'MYS' buyer_country ,
'+6066823088' buyer_contact_number ,
'1.0' einvoice_version ,
decode(aia.invoice_type_lookup_code,
'STANDARD', '11',
'CREDIT', '12',
'DEBIT', '13') einvoice_type ,
aia.invoice_num einvoice_number ,
--to_char(aia.creation_date,'DD-MM-YYYY HH:MI:SS') TRANSACTION_DATE, --EINVOICE_SUBMISSION_DATE,
to_char(aia.invoice_date, 'DD-MM-YYYY HH:MI:SS') transaction_date
--,-- As discussed with siewkim
--'' ORIGINAL_INVOICE_REF_NUMBER
/*,(case when aia.INVOICE_TYPE_LOOKUP_CODE ='DEBIT' or aia.INVOICE_TYPE_LOOKUP_CODE ='CREDIT' then
(select invoice_num
from ap_invoices_all
where 1 = 1
and INVOICE_TYPE_LOOKUP_CODE='STANDARD'
and invoice_id = (select invoice_id
from ap_invoice_lines_all
where RCV_TRANSACTION_ID=ail.RCV_TRANSACTION_ID
and invoice_id <> aia.invoice_id))
else
null
end) as ORIGINAL_INVOICE_REF_NUMBER*/
,(
CASE
WHEN aia.invoice_type_lookup_code ='DEBIT'
OR aia.invoice_type_lookup_code ='CREDIT' THEN
(
SELECT invoice_num
FROM ap_invoices_all
WHERE 1 = 1
AND invoice_type_lookup_code='STANDARD'
AND po_header_id = aia.po_header_id)
ELSE NULL
END) AS original_invoice_ref_number ,
aia.invoice_currency_code invoice_currency_code ,
--round(aia.exchange_rate,4) CURRENCY_EXCHANGE_RATE
(
SELECT DISTINCT round(glrate.conversion_rate, 4)
FROM gl_daily_rates glrate
WHERE 1 = 1
AND glrate.from_currency = aia.invoice_currency_code
AND glrate.to_currency = 'MYR'
AND glrate.conversion_type = 'Corporate'
AND trunc(glrate.conversion_date) = trunc(aia.gl_date) ) currency_exchange_rate ,
'' billing_frequency ,
'' billing_period_start_date ,
'' billing_period_end_date ,
'034' classification ,
'Self-billed - Importation of good' product_details ,
decode(ail.line_type_lookup_code,
'ITEM', ail.unit_price,
NULL) unit_price ,
'06' tax_type ,
nvl(ail.tax_rate, 0) tax_rate ,
decode(ail.line_type_lookup_code,
'TAX', ail.amount,
0) tax_amount ,
'' details_tax_exemption ,
'' amount_exempted
--,ail.AMOUNT SUBTOTAL
,
decode(ail.line_type_lookup_code,
'ITEM', ail.unit_price,
NULL)*ail.quantity_invoiced subtotal ,-- (total all line items for the invoice where line_type_lookup_code = 'ITEM')
ail.amount - decode(ail.line_type_lookup_code,
'TAX', ail.amount,
0) total_excluding_tax_l ,
ail.quantity_invoiced quantity ,
decode(upper(ail.unit_meas_lookup_code),
upper(flv.meaning), flv.tag,
'') measurement ,
'' discount_rate ,
'' discount_amount ,
'' fee_charge_rate ,
'' fee_charge_amount ,
'' product_tariff_code ,
'' country_of_origin ,
aia.invoice_amount - decode(ail.line_type_lookup_code,
'TAX', ail.amount,
0) total_excluding_tax_h ,-- to review
aia.invoice_amount total_including_tax_h ,-- to review
aia.invoice_amount total_payable_amount ,
aia.invoice_amount total_net_amount ,
'' total_discount_value ,
'' total_fee_or_charge_amount ,
'' total_tax_amount ,
'' rounding_amount ,
'' total_taxable_amount_per_tax_type ,
'' details_of_tax_exemption_h ,
'' amount_exempted_from_tax_h ,
'' tax_type_h ,
'' invoice_additional_discount_amount ,
'' invoice_additional_fee_amount ,
decode(aia.payment_method_code,
'CHECK', '02',
'EFT', '03',
'08') payment_mode ,
'' supplier_bank_account_number ,
'' payment_terms ,
--decode(ail.line_type_lookup_code, 'PREPAY','UPDATE PREPAY amount INFO HERE', 0) PREPAYMENT_AMOUNT,
0 prepayment_amount ,
'' prepayment_date ,
'' prepayment_time ,
'' prepayment_reference_number ,
'' bill_reference_number ,
-- SHIPPING RECIPIENT FIELDS ARE OPTIONAL , DO WE NEED TO PROVIDE?
'' shipping_recipient_placeholders ,
nvl(rsh.bill_of_lading, 'NA') customs_reference_number ,
nvl(pha.fob_lookup_code, 'NA') incoterms ,
'' free_trade_agreement_information ,
'' atiga_number , --AUTHORISATION NUMBER FOR CERTIFIED EXPORTER
'' customs_form_2_reference_number ,-- FOR DECLARATION OF GOODS EXPORTED
'' details_of_other_charges ,
aia.invoice_id ,
ail.line_number ,
--:P_ORG_ID as P_ORG,
to_char(:P_FROM_DATE, 'DD/MM/YYYY') AS p_from_dt ,
to_char(:P_TO_DATE, 'DD/MM/YYYY') AS p_to_dt ,
to_date(to_char(aia.creation_date, 'DD/MM/YYYY'), 'DD-MON-YYYY') asdate
-- end of required fields --
FROM ap_invoice_lines_all ail ,
----ap_suppliers aps,
poz_suppliers aps ,
----ap_supplier_sites_all ass,
poz_supplier_sites_all_m ass ,
hz_parties supplierpartyname ,
----hr_operating_units hou2,
hz_locations supplierlocation ,
fun_ou_bu_temp_v hou2 ,
----hr_locations_all_v hl,
hz_locations hl ,
xle_registrations xr ,
xle_entity_profiles xep ,
fnd_territories_tl ftt ,
po_headers_all pha ,
po_distributions_all pda ,
ap_invoice_distributions_all aid ,
rcv_shipment_lines rsl ,
rcv_shipment_headers rsh ,
rcv_transactions rt ,
fnd_territories_vl ftv ,
fnd_lookup_values_vl flv ,
ap_invoices_all aia
WHERE 1 = 1
AND aia.invoice_id = ail.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aia.vendor_id = aps.vendor_id
AND ass.vendor_id = aps.vendor_id
--AND ass.org_id = aia.org_id
AND ass.prc_bu_id = aia.org_id
AND aps.party_id = supplierpartyname.party_id
AND xr.location_id = hl.location_id
AND aia.vendor_site_id = ass.vendor_site_id
AND ass.location_id = supplierlocation.location_id
AND hou2.organization_id = aia.org_id
AND xr.source_id = hou2.default_legal_context_id
AND xr.source_id = xep.legal_entity_id
AND hl.country = ftt.territory_code
----AND TO_DATE(TO_CHAR(AIA.creation_Date,'DD-MON-YYYY'),'DD-MON-YYYY') BETWEEN :P_FROM_DATE AND :P_TO_DATE
---- AND aia.org_id =:P_ORG_ID --104
AND (
to_date(to_char(aia.creation_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') >= :P_FROM_DATE
OR :P_FROM_DATE IS NULL )
AND (
to_date(to_char(aia.creation_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') <= :P_TO_DATE
OR :P_TO_DATE IS NULL )
--AND (TO_DATE(TO_CHAR(AIA.invoice_date,'DD-MON-YYYY'),'DD-MON-YYYY') >= :P_FROM_DATE OR :P_FROM_DATE IS NULL)
--AND (TO_DATE(TO_CHAR(AIA.invoice_date,'DD-MON-YYYY'),'DD-MON-YYYY') <= :P_TO_DATE OR :P_TO_DATE IS NULL)
--AND (hou2.NAME = :P_ORG_ID OR :P_ORG_ID IS NULL)
AND hou2.name = '553 OU ON Semiconductor Malaysia Sdn. Bhd.'
AND ftt.LANGUAGE = 'US'
----AND ass.country <> 'MY'
AND supplierlocation.country <> 'MY'
AND supplierlocation.country = ftv.territory_code
AND decode(ap_invoices_utility_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code),
'FULL', 'Fully Applied',
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'UNPAID', 'Unpaid',
'AVAILABLE', 'Available',
'UNAPPROVED', 'Unvalidated',
'APPROVED', 'Validated',
'PERMANENT', 'Permanent Prepayment',
NULL) = 'Validated'
AND aia.wfapproval_status IN ( 'WFAPPROVED' ,
'MANUALLY APPROVED' ,
'NOT REQUIRED' )
AND aia.invoice_amount <> 0
--AND aia.attribute3 IS NULL
AND aia.attribute2 IS NULL
--/* added below to get customs ref number from rsh.bill_of_lading */
AND pha.po_header_id = pda.po_header_id
AND aid.po_distribution_id = pda.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND aid.rcv_transaction_id = rt.transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND pda.po_distribution_id = aid.po_distribution_id
AND (
ail.line_type_lookup_code <> 'TAX'
OR ( (
CASE ail.line_type_lookup_code
WHEN 'TAX' THEN 1
ELSE 0
END ) = 1 )
AND ail.amount <> 0 )
AND rsl.po_header_id = pha.po_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND flv.lookup_type = 'XXON_AP_EBS_IRBM_UOM_MAP'
AND upper(ail.unit_meas_lookup_code) = upper(flv.meaning)
UNION
SELECT DISTINCT 1 AS KEY ,
supplierpartyname.party_name supplier_name ,
'EI00000000030' supplier_tin ,
'BRN: NA' supplier_registration_number ,
'NA' seller_sst_reg_number ,
'NA' supplier_tourism_tax_registration_number ,
'' supplier_email ,
'26101' supplier_msic_code ,
'Manufacture of diodes, transistors and similar semiconductor devices' supplier_business_act_description ,
supplierlocation.address1 supplier_address_line1 ,
supplierlocation.address2 supplier_address_line2 ,
supplierlocation.address3 supplier_address_line3 ,
supplierlocation.postal_code supplier_zip ,
nvl(supplierlocation.city, 'NA') supplier_city ,
'17' supplier_state ,
ftv.iso_territory_code supplier_country ,
nvl(ass.phone, '+6066823088') supplier_contact_number ,
'ON Semiconductor Malaysia Sdn. Bhd.' buyer_name ,
'C898164070' buyer_tin , --xep.LEGAL_ENTITY_IDENTIFIER BUYER_TIN,
'BRN: 197901004164' buyer_reg_number ,--xr.REGISTRATION_NUMBER BUYER_REG_NUMBER,
'N101808320000468' buyer_sst_reg_number ,
'MYSE1-FIN-E-Invoicing@onsemi.com' buyer_email ,
hl.address1 buyer_address_line_1 ,
hl.address2 buyer_address_line_2 ,-- no address line2 in system
hl.address3 buyer_address_line_3 ,-- no address line3 in system
hl.postal_code buyer_zip ,
hl.city buyer_city ,
'05' buyer_state ,-- no state in system
'MYS' buyer_country ,
'+6066823088' buyer_contact_number ,
--hl.TELEPHONE_NUMBER_1 BUYER_CONTACT_NUMBER,
'1.0' einvoice_version ,
decode(aia.invoice_type_lookup_code,
'STANDARD', '11',
'CREDIT', '12',
'DEBIT', '13') einvoice_type ,
aia.invoice_num einvoice_number ,
--to_char(aia.creation_date,'DD-MM-YYYY HH:MI:SS') TRANSACTION_DATE, --EINVOICE_SUBMISSION_DATE,
to_char(aia.invoice_date, 'DD-MM-YYYY HH:MI:SS') transaction_date
--,-- as discussed with siewkim
--NULL ORIGINAL_INVOICE_REF_NUMBER
,(
CASE
WHEN aia.invoice_type_lookup_code ='DEBIT'
OR aia.invoice_type_lookup_code ='CREDIT' THEN
(
SELECT invoice_num
FROM ap_invoices_all
WHERE 1 = 1
AND invoice_type_lookup_code='STANDARD'
AND po_header_id = aia.po_header_id)
ELSE NULL
END) AS original_invoice_ref_number ,
--null ISSUER_DIGITAL_SIGNATURE,
aia.invoice_currency_code invoice_currency_code ,
--round(aia.exchange_rate,4) CURRENCY_EXCHANGE_RATE
(
SELECT DISTINCT round(glrate.conversion_rate, 4)
FROM gl_daily_rates glrate
WHERE 1 = 1
AND glrate.from_currency = aia.invoice_currency_code
AND glrate.to_currency = 'MYR'
AND glrate.conversion_type = 'Corporate'
AND trunc(glrate.conversion_date) = trunc(aia.gl_date) ) currency_exchange_rate ,
NULL billing_frequency ,
NULL billing_period_start_date ,
NULL billing_period_end_date ,
'035' classification ,
'Self-billed - Importation of services' product_details ,
decode(ail.line_type_lookup_code,
'ITEM', ail.unit_price,
NULL) unit_price ,
--decode(ail.line_type_lookup_code, 'TAX','06', null) TAX_TYPE,
'06' tax_type ,
nvl(ail.tax_rate, 0) tax_rate ,
decode(ail.line_type_lookup_code,
'TAX', ail.amount,
0) tax_amount ,
NULL details_tax_exemption ,
NULL amount_exempted
--,ail.AMOUNT SUBTOTAL
,
decode(ail.line_type_lookup_code,
'ITEM', ail.unit_price,
NULL)*ail.quantity_invoiced subtotal ,-- (total all line items for the invoice where line_type_lookup_code = 'ITEM')
ail.amount - decode(ail.line_type_lookup_code,
'TAX', ail.amount,
0) total_excluding_tax_l ,
ail.quantity_invoiced quantity ,
decode(upper(ail.unit_meas_lookup_code),
upper(flv.meaning), flv.tag,
'') measurement ,
NULL discount_rate ,
NULL discount_amount ,
NULL fee_charge_rate ,
NULL fee_charge_amount ,
NULL product_tariff_code ,
NULL country_of_origin ,
aia.invoice_amount - decode(ail.line_type_lookup_code,
'TAX', ail.amount,
0) total_excluding_tax_h ,-- to review
aia.invoice_amount total_including_tax_h ,-- to review
aia.invoice_amount total_payable_amount ,
aia.invoice_amount total_net_amount ,
NULL total_discount_value ,
NULL total_fee_or_charge_amount ,
NULL total_tax_amount ,
NULL rounding_amount ,
NULL total_taxable_amount_per_tax_type ,
NULL details_of_tax_exemption_h ,
NULL amount_exempted_from_tax_h ,
NULL tax_type_h ,
NULL invoice_additional_discount_amount ,
NULL invoice_additional_fee_amount ,
decode(aia.payment_method_code,
'CHECK', '02',
'EFT', '03',
'08') payment_mode ,
NULL supplier_bank_account_number ,
NULL payment_terms ,
--decode(ail.line_type_lookup_code, 'PREPAY','UPDATE PREPAY amount INFO HERE', 0) PREPAYMENT_AMOUNT,
0 prepayment_amount ,
NULL prepayment_date ,
NULL prepayment_time ,
NULL prepayment_reference_number ,
NULL bill_reference_number ,
NULL shipping_recipient_name ,
'NA' customs_reference_number ,
'NA' incoterms ,
NULL free_trade_agreement_information ,
NULL atiga_number , --AUTHORISATION NUMBER FOR CERTIFIED EXPORTER
NULL customs_form_2_reference_number ,-- FOR DECLARATION OF GOODS EXPORTED
NULL details_of_other_charges
-- end of required fields --
,
aia.invoice_id ,
ail.line_number ,
--:P_ORG_ID as P_ORG,
to_char(:P_FROM_DATE, 'DD/MM/YYYY') AS p_from_dt ,
to_char(:P_TO_DATE, 'DD/MM/YYYY') AS p_to_dt ,
to_date(to_char(aia.creation_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') asdate
--, ail.line_type_lookup_code, aid.distribution_line_number, aid.dist_match_type
FROM ap_invoices_all aia ,
ap_invoice_lines_all ail ,
--ap_suppliers aps,
poz_suppliers aps ,
--ap_supplier_sites_all ass,
poz_supplier_sites_all_m ass ,
hz_locations supplierlocation ,
--hr_operating_units hou2,
fun_ou_bu_temp_v hou2 ,
--hr_locations_all_v hl,
hz_locations hl ,
hz_parties supplierpartyname ,
xle_registrations xr ,
xle_entity_profiles xep ,
fnd_territories_tl ftt ,
ap_invoice_distributions_all aid ,
fnd_territories_vl ftv ,
fnd_lookup_values_vl flv
WHERE 1 = 1
AND aia.invoice_id = ail.invoice_id
AND aia.vendor_id = aps.vendor_id
AND ass.vendor_id = aps.vendor_id
--AND ass.org_id = aia.org_id
AND ass.prc_bu_id = aia.org_id
AND ass.location_id = supplierlocation.location_id
AND xr.location_id = hl.location_id
AND aia.vendor_site_id = ass.vendor_site_id
AND hou2.organization_id = aia.org_id
AND xr.source_id = hou2.default_legal_context_id
AND xr.source_id = xep.legal_entity_id
AND hl.country = ftt.territory_code
AND aps.party_id = supplierpartyname.party_id
AND (
to_date(to_char(aia.creation_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') >= :P_FROM_DATE
OR :P_FROM_DATE IS NULL )
AND (
to_date(to_char(aia.creation_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') <= :P_TO_DATE
OR :P_TO_DATE IS NULL )
--AND (TO_DATE(TO_CHAR(AIA.invoice_date,'DD-MON-YYYY'),'DD-MON-YYYY') >= :P_FROM_DATE OR :P_FROM_DATE IS NULL)
--AND (TO_DATE(TO_CHAR(AIA.invoice_date,'DD-MON-YYYY'),'DD-MON-YYYY') <= :P_TO_DATE OR :P_TO_DATE IS NULL)
--AND aia.org_id =:P_ORG_ID OR :P_ORG_ID IS NULL
--AND (hou2.NAME = :P_ORG_ID OR :P_ORG_ID IS NULL)
AND hou2.name = '553 OU ON Semiconductor Malaysia Sdn. Bhd.'
AND ftt.LANGUAGE = 'US'
--AND ass.country <> 'MY'
--and ass.country = ftv.territory_code
AND supplierlocation.country <> 'MY'
AND supplierlocation.country = ftv.territory_code
AND decode(ap_invoices_utility_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code),
'FULL', 'Fully Applied',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'NEVER APPROVED', 'Never Validated',
'CANCELLED', 'Cancelled',
'UNPAID', 'Unpaid',
'AVAILABLE', 'Available',
'UNAPPROVED', 'Unvalidated',
'APPROVED', 'Validated',
'PERMANENT', 'Permanent Prepayment',
NULL) = 'Validated'
AND aia.wfapproval_status IN ( 'WFAPPROVED' ,
'MANUALLY APPROVED' ,
'NOT REQUIRED' )
AND aia.invoice_amount <> 0
--AND aia.attribute3 IS NULL
AND aia.attribute2 IS NULL
AND aia.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aid.dist_match_type = 'ITEM_TO_PO'
AND (
ail.line_type_lookup_code <> 'TAX'
OR ( (
CASE ail.line_type_lookup_code
WHEN 'TAX' THEN 1
ELSE 0
END ) = 1 )
AND ail.amount <> 0 )
AND flv.lookup_type = 'XXON_AP_EBS_IRBM_UOM_MAP'
AND upper(ail.unit_meas_lookup_code) = upper(flv.meaning)
ORDER BY invoice_id ,
line_number
---AP EBTAX Rates DMAP EBTAX Rates Report
---AP EBTAX Rates Report---
SELECT txc.tax tax_code,
txn.tax_full_name tax_name,
txc.tax_type_code tax_type,
txr.tax_rate_code tax_rate_code,
txr.tax_regime_code tax_regime,
ter.territory_short_name country,
txr.percentage_rate percentage,
rcv.percentage_rate recovery_pct,
to_char(txr.effective_from,'DD-MON-YYYY HH24:MI:SS AM') effective_from,
to_char(txr.effective_to,'DD-MON-YYYY HH24:MI:SS AM') effective_to,
txr.offset_tax offset_tax_code,
txr.default_rec_rate_code recovery_code,
glc.concatenated_segments account_combination,
to_char(txc.creation_date,'DD-MON-YYYY HH24:MI:SS AM') creation_date,
to_char(txc.last_update_date,'DD-MON-YYYY HH24:MI:SS AM') last_updt_date,
usr.user_id last_updt_id,
usr.username last_updt_name,
txc.tax_id tax_id
FROM zx_taxes_b txc,
zx_taxes_tl txn,
zx_rates_b txr,
zx_rates_b rcv,
zx_accounts txa,
zx_regimes_b rgm,
gl_code_combinations glc,
fnd_territories_tl ter,
per_users usr
WHERE txn.tax_id = txc.tax_id
AND txn.LANGUAGE = 'US'
AND txr.tax = txc.tax
AND txr.rate_type_code = 'PERCENTAGE'
AND rcv.rate_type_code = 'RECOVERY'
AND rcv.tax = txr.tax
AND txa.tax_account_entity_id = txr.tax_rate_id
AND glc.code_combination_id = txa.tax_account_ccid
AND rgm.tax_regime_code = txr.tax_regime_code
AND ter.LANGUAGE = 'US'
AND ter.territory_code = rgm.country_code
AND usr.username = txc.last_updated_by
AND trunc(SYSDATE) BETWEEN nvl(txc.effective_from, trunc(SYSDATE)) AND nvl(txc.effective_to, trunc(SYSDATE))
AND trunc(SYSDATE) BETWEEN nvl(txr.effective_from, trunc(SYSDATE)) AND nvl(txr.effective_to, trunc(SYSDATE))
AND trunc(SYSDATE) BETWEEN nvl(rcv.effective_from, trunc(SYSDATE)) AND nvl(rcv.effective_to, trunc(SYSDATE))
/*
AND Txc.Tax = nvl(:P_TAX,Txc.Tax)
AND Txr.Tax_Regime_Code = nvl(:P_TAX_REGIME_CODE,Txr.Tax_Regime_Code)
AND Ter.Territory_Short_Name = nvl(:P_TERRITORY_SHORT_NAME,Ter.Territory_Short_Name)
*/
AND (
CASE
WHEN txc.tax IN (:P_TAX) THEN 1
WHEN (
coalesce(NULL,:P_TAX) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN txr.tax_regime_code IN (:P_TAX_REGIME_CODE) THEN 1
WHEN (
coalesce(NULL,:P_TAX_REGIME_CODE) IS NULL ) THEN 1
END = 1 )
AND (
CASE
WHEN ter.territory_short_name IN (:P_TERRITORY_SHORT_NAME) THEN 1
WHEN (
coalesce(NULL,:P_TERRITORY_SHORT_NAME) IS NULL ) THEN 1
END = 1 )
No comments:
Post a Comment