((SELECT
--distinct
rct.customer_trx_id main_id,
aps.payment_schedule_id,
aps.CLASS,
rct.complete_flag status,
ac.customer_number,
ac.customer_number customer_number1,
ac.customer_id,
rct.bill_to_customer_id,
ac.customer_name,
upper(ac.customer_name) customer_name2,
rct.trx_number,
rct.customer_trx_id,
su_bill.cust_acct_site_id,
raa_bill.cust_acct_site_id,
raa_bill.party_site_id,
raa_bill_loc.address1
|| ' , '
|| raa_bill_loc.address2
|| ' , '
|| raa_bill_loc.address3 bill_to_address,
raa_bill_loc.country bill_to_country_code,
ftt_bill.territory_short_name bill_to_country,
raa_ship_loc.address1
|| ' , '
|| raa_ship_loc.address2
|| ' , '
|| raa_ship_loc.address3 ship_to_address,
raa_ship_loc.country ship_to_country_code,
ftt_ship.territory_short_name ship_to_country,
rct.ship_to_site_use_id,
su_ship.site_use_id,
msif.segment1 || '-' || msif.segment2 item,
msif.description item_description,
TO_CHAR (TO_DATE (NVL (wnd.confirm_date, wnd.last_update_date),
'DD-MON-RRRR'),
'DD-MON-RRRR') delivery_date,
wnd.delivery_id delivery_id,
rct.org_id,
(select hpc.SUB_PERSON_PRE_NAME_ADJUNCT||' '||hpc.SUB_PERSON_FIRST_NAME||' '||hpc.SUB_PERSON_LAST_NAME
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_person,
(select hpc.REL_EMAIL_ADDRESS
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) mail_id,
(select hpc.REL_PHONE_COUNTRY_CODE||'-'||hpc.REL_PHONE_AREA_CODE||'-'||hpc.REL_PHONE_NUMBER
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_number,
msif.organization_id,
jatl.excise_invoice_no,
jatl.excise_invoice_date,
rct.trx_number ar_invoice_no,
rct.trx_date,
rct.exchange_rate,
TO_CHAR (TO_DATE (rct.trx_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
ar_invoice_date,
-- TO_CHAR (TO_DATE (rct.term_due_date, 'DD-MON-RRRR'),
-- 'DD-MON-RRRR'
-- ) due_date,
TO_CHAR (TO_DATE (aps.due_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') due_date,
rctt.NAME,
TO_CHAR (TO_DATE (gd.gl_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') gl_date,
rctl.sales_order,
--rct.ct_reference,
ool.line_number sales_order_line_no,
ool.attribute4 ci_no,
ool.attribute5 ci_date,
rctl.interface_line_attribute2 sales_order_type,
rt.NAME payment_term,
rt.description payment_term_description,
ooh.cust_po_number,
ooh.request_date po_date1,
ooh.ordered_date po_date2,
--ph.submit_date po_date,
-- TO_CHAR (TO_DATE (ph.creation_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
-- po_date,
ooh.attribute1 po_date,
rctl.uom_code,
rct.invoice_currency_code currency,
NVL (
(SELECT SUM (rctl1.extended_amount)
FROM ra_customer_trx_lines rctl1
WHERE rctl1.customer_trx_id = rctl.customer_trx_id),jat.total_amount)
invoice_amount,
gcc.segment7,
ffv.description intercompany,
-- ool.shipped_quantity,
-- SUM (ool.shipped_quantity) OVER (PARTITION BY ool.header_id)
-- total_shipped_quantity,
-- (SELECT SUM (ool1.shipped_quantity)
-- FROM oe_order_lines ool1
-- WHERE ool1.header_id = ooh.header_id
-- GROUP BY ool1.header_id) total_shipped_quantity,
(SELECT SUM (wdd1.shipped_quantity)
FROM wsh_delivery_assignments wda1, wsh_delivery_details wdd1
WHERE wda1.delivery_detail_id = wdd1.delivery_detail_id
AND to_char(wda1.delivery_id) IN (
SELECT DISTINCT rctl1.interface_line_attribute3
FROM ra_customer_trx_lines_all rctl1
WHERE rctl1.customer_trx_id = rctl.customer_trx_id
AND rctl1.line_type IN ('LINE', 'CB')))
total_shipped_quantity,
-------------------------------------------------------------------------------------------------
-- Fiscal Period and Year
-------------------------------------------------------------------------------------------------
(SELECT per1.period_year
FROM gl_periods per1
WHERE per1.period_set_name = 'DK_CALENDAR'
AND per1.adjustment_period_flag = 'N'
AND (TO_DATE (gd.gl_date, 'DD-MON-RRRR')
BETWEEN TO_DATE (per1.start_date, 'DD-MON-RRRR')
AND TO_DATE (per1.end_date, 'DD-MON-RRRR')
)) fiscal_period,
(SELECT per2.period_name
FROM gl_periods per2
WHERE per2.period_set_name = 'DK_CALENDAR'
AND per2.adjustment_period_flag = 'N'
AND (TO_DATE (gd.gl_date, 'DD-MON-RRRR')
BETWEEN TO_DATE (per2.start_date, 'DD-MON-RRRR')
AND TO_DATE (per2.end_date, 'DD-MON-RRRR')
)) fiscal_year,
-------------------------------------------------------------------------------------------------
-- End of Fiscal Period and Year
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- C Form
-------------------------------------------------------------------------------------------------
(SELECT 'YES'
FROM DUAL
WHERE EXISTS (SELECT jasmdgv.trx_number
FROM jai_ap_st_match_dtl_grprcpt_v jasmdgv
WHERE jasmdgv.trx_number = rct.trx_number)) c_form,
-------------------------------------------------------------------------------------------------
-- End of C Form
-------------------------------------------------------------------------------------------------
rct.trx_number,
ooh.order_number, -- ool.header_id,
aps.amount_due_original original_amount,
aps.amount_applied amount_recieved,
aps.amount_credited credit_refunds,
aps.amount_adjusted adjustments,
NULL assignment,
aps.discount_taken_earned discounts,
aps.amount_due_remaining bal_amt_in_entered_cur,
ac.primary_salesrep_id
FROM ar_customers ac,
ra_customer_trx rct,
-- hz_cust_accounts hza,
-- hz_parties hp,
-------------------------------------------------------------------------------------------------
-- Bill to address
-------------------------------------------------------------------------------------------------
hz_cust_site_uses su_bill,
hz_cust_acct_sites raa_bill,
hz_party_sites raa_bill_ps,
hz_locations raa_bill_loc,
fnd_territories_tl ftt_bill,
-------------------------------------------------------------------------------------------------
-- End of bill to address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Ship to Address
-------------------------------------------------------------------------------------------------
hz_cust_site_uses_all su_ship,
hz_cust_acct_sites raa_ship,
hz_party_sites raa_ship_ps,
hz_locations raa_ship_loc,
fnd_territories_tl ftt_ship,
-------------------------------------------------------------------------------------------------
-- End of Ship to Address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Item Description & Sales Details
-------------------------------------------------------------------------------------------------
ra_customer_trx_lines rctl,
mtl_system_items_fvl msif,
-------------------------------------------------------------------------------------------------
-- End of Item Description & Sales Details
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Excise Invoice
-------------------------------------------------------------------------------------------------
jai_ar_trx_lines jatl,
-------------------------------------------------------------------------------------------------
-- End of Excise Invoice
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Document Type
-------------------------------------------------------------------------------------------------
ra_cust_trx_types rctt,
-------------------------------------------------------------------------------------------------
-- End of Document Type
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- GL Date
-------------------------------------------------------------------------------------------------
ra_cust_trx_line_gl_dist gd,
-------------------------------------------------------------------------------------------------
-- End of GL Date
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Payment Terms
-------------------------------------------------------------------------------------------------
ra_terms rt,
-------------------------------------------------------------------------------------------------
-- End of Payment Terms
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
oe_order_headers ooh,
po_headers ph,
oe_order_lines ool,
-------------------------------------------------------------------------------------------------
-- End of Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Invoice Amount
-------------------------------------------------------------------------------------------------
jai_ar_trxs jat,
-------------------------------------------------------------------------------------------------
-- End of Invoice Amount
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Inter-company
-------------------------------------------------------------------------------------------------
gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
-------------------------------------------------------------------------------------------------
-- End of Inter-company
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
ar_payment_schedules aps,
-------------------------------------------------------------------------------------------------
-- End of Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
wsh_new_deliveries wnd
-------------------------------------------------------------------------------------------------
-- End of Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
WHERE ac.customer_id = rct.bill_to_customer_id
AND ac.status = 'A'
AND rct.complete_flag = 'Y'
-- AND rct.bill_to_customer_id = hza.cust_account_id
-- AND hza.party_id = hp.party_id
-------------------------------------------------------------------------------------------------
-- Bill to address
-------------------------------------------------------------------------------------------------
AND rct.bill_to_site_use_id = su_bill.site_use_id
AND su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
AND raa_bill.party_site_id = raa_bill_ps.party_site_id
AND raa_bill_ps.location_id = raa_bill_loc.location_id
AND raa_bill_loc.country = ftt_bill.territory_code
-------------------------------------------------------------------------------------------------
-- End of bill to address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Ship to Address
-------------------------------------------------------------------------------------------------
AND rct.ship_to_site_use_id = su_ship.site_use_id(+)
AND su_ship.cust_acct_site_id = raa_ship.cust_acct_site_id(+)
AND raa_ship.party_site_id = raa_ship_ps.party_site_id(+)
AND raa_ship_ps.location_id = raa_ship_loc.location_id(+)
AND raa_bill_loc.country = ftt_ship.territory_code
-------------------------------------------------------------------------------------------------
-- End of Ship to Address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Item Description & Sales Details
-------------------------------------------------------------------------------------------------
AND rct.customer_trx_id = rctl.customer_trx_id
AND UPPER (rctl.line_type) IN ('LINE', 'CB')
AND rctl.inventory_item_id = msif.inventory_item_id(+)
AND rctl.warehouse_id = msif.organization_id(+)
-- AND (rctl.sales_order = rct.ct_reference OR rct.ct_reference is null)
AND rctl.line_number =
(SELECT MIN (rctl1.line_number)
FROM ra_customer_trx_lines rctl1
WHERE rctl.customer_trx_id = rctl1.customer_trx_id
AND UPPER (rctl1.line_type) IN ('LINE', 'CB')
GROUP BY rctl1.customer_trx_id)
-------------------------------------------------------------------------------------------------
-- End of Item Description & Sales Details
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Excise Invoice
-------------------------------------------------------------------------------------------------
AND rctl.customer_trx_id = jatl.customer_trx_id(+)
AND rctl.customer_trx_line_id = jatl.customer_trx_line_id(+)
AND rctl.inventory_item_id = jatl.inventory_item_id(+)
-------------------------------------------------------------------------------------------------
-- End of Excise Invoice
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Document Type
-------------------------------------------------------------------------------------------------
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
-------------------------------------------------------------------------------------------------
-- End of Document Type
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- GL Date
-------------------------------------------------------------------------------------------------
AND rct.customer_trx_id = gd.customer_trx_id
AND gd.account_class IN ('REC', 'REV')
AND 'Y' = gd.latest_rec_flag
-------------------------------------------------------------------------------------------------
-- End of GL Date
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Payment Terms
-------------------------------------------------------------------------------------------------
AND rct.term_id = rt.term_id(+)
-------------------------------------------------------------------------------------------------
-- End of Payment Terms
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
AND rctl.sales_order = ooh.order_number(+)
AND ooh.cust_po_number = ph.segment1(+)
AND rctl.interface_line_attribute6 = ool.line_id(+)
-------------------------------------------------------------------------------------------------
-- End of Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Invoice Amount
-------------------------------------------------------------------------------------------------
AND rct.trx_number = jat.trx_number(+)
AND rct.customer_trx_id = jat.customer_trx_id(+)
-------------------------------------------------------------------------------------------------
-- End of Invoice Amount
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Inter-company
-------------------------------------------------------------------------------------------------
AND gd.code_combination_id = gcc.code_combination_id
AND gcc.segment7 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = 'DK_INTERCOMPANY'
-------------------------------------------------------------------------------------------------
-- End of Inter-company
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
AND rct.trx_number = aps.trx_number
AND rct.customer_trx_id = aps.customer_trx_id
-------------------------------------------------------------------------------------------------
-- End of Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
AND to_char(wnd.delivery_id(+)) = rctl.interface_line_attribute3
-------------------------------------------------------------------------------------------------
-- End of Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Report Parameters
-------------------------------------------------------------------------------------------------
&pl_customer_number
&Pl_COUNTRY
&pl_salesrepid
--&pl_include_fully_paid_invoices
&pl_due_date
&pl_invoice_date
&pl_gl_date_inv
-------------------------------------------------------------------------------------------------
-- End of Report Parameters
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Test Conditions
-------------------------------------------------------------------------------------------------
--and rct.TRX_NUMBER = 'DOI20100553'
--AND ac.customer_number = 1060
--and rownum < 1000
--and ooh.order_number = 2110010103
--AND rct.trx_number = '211000259'
--and aps.PAYMENT_SCHEDULE_ID IN( 1112,1113,1114,1115)
--and rct.customer_trx_id = 1110
--and aps.amount_applied is not null
-------------------------------------------------------------------------------------------------
-- End of Test Conditions
-------------------------------------------------------------------------------------------------
)
UNION
(SELECT
--distinct
acr.cash_receipt_id main_id,
aps.payment_schedule_id,
aps.CLASS,
acr.status,
ac.customer_number,
ac.customer_number customer_number1,
ac.customer_id,
NULL bill_to_customer_id,
ac.customer_name,
upper(ac.customer_name) customer_name2,
NULL trx_number,
NULL customer_trx_id,
NULL cust_acct_site_id,
NULL cust_acct_site_id,
NULL party_site_id,
NULL bill_to_address,
NULL bill_to_country_code,
NULL bill_to_country,
NULL ship_to_address,
NULL ship_to_country_code,
NULL ship_to_country,
NULL ship_to_site_use_id,
NULL site_use_id,
NULL item,
NULL item_description,
NULL delivery_date,
NULL delivery_id,
NULL org_id,
(select hpc.SUB_PERSON_PRE_NAME_ADJUNCT||' '||hpc.SUB_PERSON_FIRST_NAME||' '||hpc.SUB_PERSON_LAST_NAME
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_person,
(select hpc.REL_EMAIL_ADDRESS
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) mail_id,
(select hpc.REL_PHONE_COUNTRY_CODE||'-'||hpc.REL_PHONE_AREA_CODE||'-'||hpc.REL_PHONE_NUMBER
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_number,
NULL organization_id,
NULL excise_invoice_no,
NULL excise_invoice_date,
acr.receipt_number ar_invoice_no,
acr.receipt_date trx_date,
acr.exchange_rate,
TO_CHAR (TO_DATE (acr.receipt_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
ar_invoice_date,
TO_CHAR (TO_DATE (aps.due_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') due_date,
'Receipt' NAME,
TO_CHAR (TO_DATE (acrh.gl_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') gl_date,
NULL sales_order,
NULL sales_order_line_no,
NULL ci_no,
NULL ci_date,
NULL sales_order_type,
NULL payment_term,
NULL payment_term_description,
NULL cust_po_number,
NULL po_date1,
NULL po_date2,
NULL po_date,
NULL uom_code,
acr.currency_code currency,
acr.amount invoice_amount,
NULL segment7,
NULL intercompany,
-- ool.shipped_quantity,
-- SUM (ool.shipped_quantity) OVER (PARTITION BY ool.header_id)
-- total_shipped_quantity,
NULL total_shipped_quantity,
NULL fiscal_period,
NULL fiscal_year,
NULL c_form,
NULL trx_number,
NULL order_number,
aps.amount_due_original original_amount,
aps.amount_applied amount_recieved,
aps.amount_credited credit_refunds,
aps.amount_adjusted adjustments,
NULL assignment,
aps.discount_taken_earned discounts,
aps.amount_due_remaining bal_amt_in_entered_cur,
ac.primary_salesrep_id
FROM ar_customers ac,
hz_cust_accounts hca,
ar_cash_receipts acr,
ar_payment_schedules aps,
ar_cash_receipt_history_all acrh
WHERE ac.customer_id = hca.cust_account_id
AND acr.pay_from_customer = hca.cust_account_id
AND aps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = acrh.cash_receipt_id
AND acrh.first_posted_record_flag(+) = 'Y'
-- AND aps.amount_due_remaining <> 0
-- AND aps.amount_due_remaining IS NOT NULL
-- AND acr.status <> 'REV'
AND NOT EXISTS
(select acrh1.status
from
ar_cash_receipt_history_all acrh1
where acr.cash_receipt_id = acrh1.cash_receipt_id
and acrh1.status = 'REVERSED'
)
-------------------------------------------------------------------------------------------------
-- Report Parameters
-------------------------------------------------------------------------------------------------
&pl_customer_number
&pl_salesrepid
&pl_due_date
&pl_receipt_date
&pl_gl_date_rcpt
-------------------------------------------------------------------------------------------------
-- End of Report Parameters
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Test Conditions
-------------------------------------------------------------------------------------------------
--AND ac.customer_number = 1060
--and rownum < 1000
--and acr.RECEIPT_NUMBER in ('22222','100')
-------------------------------------------------------------------------------------------------
-- End of Test Conditions
-------------------------------------------------------------------------------------------------
)
UNION
(SELECT
--distinct
acr.cash_receipt_id main_id,
aps.payment_schedule_id,
aps.CLASS,
acrh.status,
ac.customer_number,
ac.customer_number customer_number1,
ac.customer_id,
NULL bill_to_customer_id,
ac.customer_name,
upper(ac.customer_name) customer_name2,
NULL trx_number,
NULL customer_trx_id,
NULL cust_acct_site_id,
NULL cust_acct_site_id,
NULL party_site_id,
NULL bill_to_address,
NULL bill_to_country_code,
NULL bill_to_country,
NULL ship_to_address,
NULL ship_to_country_code,
NULL ship_to_country,
NULL ship_to_site_use_id,
NULL site_use_id,
NULL item,
NULL item_description,
NULL delivery_date,
NULL delivery_id,
NULL org_id,
(select hpc.SUB_PERSON_PRE_NAME_ADJUNCT||' '||hpc.SUB_PERSON_FIRST_NAME||' '||hpc.SUB_PERSON_LAST_NAME
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_person,
(select hpc.REL_EMAIL_ADDRESS
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) mail_id,
(select hpc.REL_PHONE_COUNTRY_CODE||'-'||hpc.REL_PHONE_AREA_CODE||'-'||hpc.REL_PHONE_NUMBER
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_number,
NULL organization_id,
NULL excise_invoice_no,
NULL excise_invoice_date,
acr.receipt_number ar_invoice_no,
acr.receipt_date trx_date,
acr.exchange_rate,
TO_CHAR (TO_DATE (acr.receipt_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
ar_invoice_date,
TO_CHAR (TO_DATE (aps.due_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') due_date,
'Receipt' NAME,
TO_CHAR (TO_DATE (acrh1.gl_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') gl_date,
NULL sales_order,
NULL sales_order_line_no,
NULL ci_no,
NULL ci_date,
NULL sales_order_type,
NULL payment_term,
NULL payment_term_description,
NULL cust_po_number,
NULL po_date1,
NULL po_date2,
NULL po_date,
NULL uom_code,
acr.currency_code currency,
acr.amount invoice_amount,
NULL segment7,
NULL intercompany,
-- ool.shipped_quantity,
-- SUM (ool.shipped_quantity) OVER (PARTITION BY ool.header_id)
-- total_shipped_quantity,
NULL total_shipped_quantity,
NULL fiscal_period,
NULL fiscal_year,
NULL c_form,
NULL trx_number,
NULL order_number,
aps.amount_due_original original_amount,
aps.amount_applied amount_recieved,
aps.amount_credited credit_refunds,
aps.amount_adjusted adjustments,
NULL assignment,
aps.discount_taken_earned discounts,
aps.amount_due_remaining bal_amt_in_entered_cur,
ac.primary_salesrep_id
FROM ar_customers ac,
hz_cust_accounts hca,
ar_cash_receipts acr,
ar_cash_receipt_history_all acrh,
ar_cash_receipt_history_all acrh1,
ar_payment_schedules aps
WHERE ac.customer_id = hca.cust_account_id
AND acr.pay_from_customer = hca.cust_account_id
AND acr.cash_receipt_id = acrh.cash_receipt_id(+)
AND acrh.status = 'REVERSED'
AND acr.cash_receipt_id = acrh1.cash_receipt_id(+)
ANd acrh1.status = 'CLEARED'
AND aps.cash_receipt_id = acr.cash_receipt_id
AND acr.receipt_date <> acr.reversal_date
-------------------------------------------------------------------------------------------------
-- Report Parameters
-------------------------------------------------------------------------------------------------
&pl_customer_number
&pl_salesrepid
&pl_due_date
&pl_receipt_date
&pl_gl_date_rev_rcpt
-------------------------------------------------------------------------------------------------
-- End of Report Parameters
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Test Conditions
-------------------------------------------------------------------------------------------------
--AND ac.customer_number = 1060
--and rownum < 1000
--and acr.RECEIPT_NUMBER in ('22222','100')
-------------------------------------------------------------------------------------------------
-- End of Test Conditions
-------------------------------------------------------------------------------------------------
))
--distinct
rct.customer_trx_id main_id,
aps.payment_schedule_id,
aps.CLASS,
rct.complete_flag status,
ac.customer_number,
ac.customer_number customer_number1,
ac.customer_id,
rct.bill_to_customer_id,
ac.customer_name,
upper(ac.customer_name) customer_name2,
rct.trx_number,
rct.customer_trx_id,
su_bill.cust_acct_site_id,
raa_bill.cust_acct_site_id,
raa_bill.party_site_id,
raa_bill_loc.address1
|| ' , '
|| raa_bill_loc.address2
|| ' , '
|| raa_bill_loc.address3 bill_to_address,
raa_bill_loc.country bill_to_country_code,
ftt_bill.territory_short_name bill_to_country,
raa_ship_loc.address1
|| ' , '
|| raa_ship_loc.address2
|| ' , '
|| raa_ship_loc.address3 ship_to_address,
raa_ship_loc.country ship_to_country_code,
ftt_ship.territory_short_name ship_to_country,
rct.ship_to_site_use_id,
su_ship.site_use_id,
msif.segment1 || '-' || msif.segment2 item,
msif.description item_description,
TO_CHAR (TO_DATE (NVL (wnd.confirm_date, wnd.last_update_date),
'DD-MON-RRRR'),
'DD-MON-RRRR') delivery_date,
wnd.delivery_id delivery_id,
rct.org_id,
(select hpc.SUB_PERSON_PRE_NAME_ADJUNCT||' '||hpc.SUB_PERSON_FIRST_NAME||' '||hpc.SUB_PERSON_LAST_NAME
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_person,
(select hpc.REL_EMAIL_ADDRESS
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) mail_id,
(select hpc.REL_PHONE_COUNTRY_CODE||'-'||hpc.REL_PHONE_AREA_CODE||'-'||hpc.REL_PHONE_NUMBER
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_number,
msif.organization_id,
jatl.excise_invoice_no,
jatl.excise_invoice_date,
rct.trx_number ar_invoice_no,
rct.trx_date,
rct.exchange_rate,
TO_CHAR (TO_DATE (rct.trx_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
ar_invoice_date,
-- TO_CHAR (TO_DATE (rct.term_due_date, 'DD-MON-RRRR'),
-- 'DD-MON-RRRR'
-- ) due_date,
TO_CHAR (TO_DATE (aps.due_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') due_date,
rctt.NAME,
TO_CHAR (TO_DATE (gd.gl_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') gl_date,
rctl.sales_order,
--rct.ct_reference,
ool.line_number sales_order_line_no,
ool.attribute4 ci_no,
ool.attribute5 ci_date,
rctl.interface_line_attribute2 sales_order_type,
rt.NAME payment_term,
rt.description payment_term_description,
ooh.cust_po_number,
ooh.request_date po_date1,
ooh.ordered_date po_date2,
--ph.submit_date po_date,
-- TO_CHAR (TO_DATE (ph.creation_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
-- po_date,
ooh.attribute1 po_date,
rctl.uom_code,
rct.invoice_currency_code currency,
NVL (
(SELECT SUM (rctl1.extended_amount)
FROM ra_customer_trx_lines rctl1
WHERE rctl1.customer_trx_id = rctl.customer_trx_id),jat.total_amount)
invoice_amount,
gcc.segment7,
ffv.description intercompany,
-- ool.shipped_quantity,
-- SUM (ool.shipped_quantity) OVER (PARTITION BY ool.header_id)
-- total_shipped_quantity,
-- (SELECT SUM (ool1.shipped_quantity)
-- FROM oe_order_lines ool1
-- WHERE ool1.header_id = ooh.header_id
-- GROUP BY ool1.header_id) total_shipped_quantity,
(SELECT SUM (wdd1.shipped_quantity)
FROM wsh_delivery_assignments wda1, wsh_delivery_details wdd1
WHERE wda1.delivery_detail_id = wdd1.delivery_detail_id
AND to_char(wda1.delivery_id) IN (
SELECT DISTINCT rctl1.interface_line_attribute3
FROM ra_customer_trx_lines_all rctl1
WHERE rctl1.customer_trx_id = rctl.customer_trx_id
AND rctl1.line_type IN ('LINE', 'CB')))
total_shipped_quantity,
-------------------------------------------------------------------------------------------------
-- Fiscal Period and Year
-------------------------------------------------------------------------------------------------
(SELECT per1.period_year
FROM gl_periods per1
WHERE per1.period_set_name = 'DK_CALENDAR'
AND per1.adjustment_period_flag = 'N'
AND (TO_DATE (gd.gl_date, 'DD-MON-RRRR')
BETWEEN TO_DATE (per1.start_date, 'DD-MON-RRRR')
AND TO_DATE (per1.end_date, 'DD-MON-RRRR')
)) fiscal_period,
(SELECT per2.period_name
FROM gl_periods per2
WHERE per2.period_set_name = 'DK_CALENDAR'
AND per2.adjustment_period_flag = 'N'
AND (TO_DATE (gd.gl_date, 'DD-MON-RRRR')
BETWEEN TO_DATE (per2.start_date, 'DD-MON-RRRR')
AND TO_DATE (per2.end_date, 'DD-MON-RRRR')
)) fiscal_year,
-------------------------------------------------------------------------------------------------
-- End of Fiscal Period and Year
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- C Form
-------------------------------------------------------------------------------------------------
(SELECT 'YES'
FROM DUAL
WHERE EXISTS (SELECT jasmdgv.trx_number
FROM jai_ap_st_match_dtl_grprcpt_v jasmdgv
WHERE jasmdgv.trx_number = rct.trx_number)) c_form,
-------------------------------------------------------------------------------------------------
-- End of C Form
-------------------------------------------------------------------------------------------------
rct.trx_number,
ooh.order_number, -- ool.header_id,
aps.amount_due_original original_amount,
aps.amount_applied amount_recieved,
aps.amount_credited credit_refunds,
aps.amount_adjusted adjustments,
NULL assignment,
aps.discount_taken_earned discounts,
aps.amount_due_remaining bal_amt_in_entered_cur,
ac.primary_salesrep_id
FROM ar_customers ac,
ra_customer_trx rct,
-- hz_cust_accounts hza,
-- hz_parties hp,
-------------------------------------------------------------------------------------------------
-- Bill to address
-------------------------------------------------------------------------------------------------
hz_cust_site_uses su_bill,
hz_cust_acct_sites raa_bill,
hz_party_sites raa_bill_ps,
hz_locations raa_bill_loc,
fnd_territories_tl ftt_bill,
-------------------------------------------------------------------------------------------------
-- End of bill to address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Ship to Address
-------------------------------------------------------------------------------------------------
hz_cust_site_uses_all su_ship,
hz_cust_acct_sites raa_ship,
hz_party_sites raa_ship_ps,
hz_locations raa_ship_loc,
fnd_territories_tl ftt_ship,
-------------------------------------------------------------------------------------------------
-- End of Ship to Address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Item Description & Sales Details
-------------------------------------------------------------------------------------------------
ra_customer_trx_lines rctl,
mtl_system_items_fvl msif,
-------------------------------------------------------------------------------------------------
-- End of Item Description & Sales Details
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Excise Invoice
-------------------------------------------------------------------------------------------------
jai_ar_trx_lines jatl,
-------------------------------------------------------------------------------------------------
-- End of Excise Invoice
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Document Type
-------------------------------------------------------------------------------------------------
ra_cust_trx_types rctt,
-------------------------------------------------------------------------------------------------
-- End of Document Type
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- GL Date
-------------------------------------------------------------------------------------------------
ra_cust_trx_line_gl_dist gd,
-------------------------------------------------------------------------------------------------
-- End of GL Date
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Payment Terms
-------------------------------------------------------------------------------------------------
ra_terms rt,
-------------------------------------------------------------------------------------------------
-- End of Payment Terms
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
oe_order_headers ooh,
po_headers ph,
oe_order_lines ool,
-------------------------------------------------------------------------------------------------
-- End of Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Invoice Amount
-------------------------------------------------------------------------------------------------
jai_ar_trxs jat,
-------------------------------------------------------------------------------------------------
-- End of Invoice Amount
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Inter-company
-------------------------------------------------------------------------------------------------
gl_code_combinations gcc,
fnd_flex_values_vl ffv,
fnd_flex_value_sets ffvs,
-------------------------------------------------------------------------------------------------
-- End of Inter-company
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
ar_payment_schedules aps,
-------------------------------------------------------------------------------------------------
-- End of Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
wsh_new_deliveries wnd
-------------------------------------------------------------------------------------------------
-- End of Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
WHERE ac.customer_id = rct.bill_to_customer_id
AND ac.status = 'A'
AND rct.complete_flag = 'Y'
-- AND rct.bill_to_customer_id = hza.cust_account_id
-- AND hza.party_id = hp.party_id
-------------------------------------------------------------------------------------------------
-- Bill to address
-------------------------------------------------------------------------------------------------
AND rct.bill_to_site_use_id = su_bill.site_use_id
AND su_bill.cust_acct_site_id = raa_bill.cust_acct_site_id
AND raa_bill.party_site_id = raa_bill_ps.party_site_id
AND raa_bill_ps.location_id = raa_bill_loc.location_id
AND raa_bill_loc.country = ftt_bill.territory_code
-------------------------------------------------------------------------------------------------
-- End of bill to address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Ship to Address
-------------------------------------------------------------------------------------------------
AND rct.ship_to_site_use_id = su_ship.site_use_id(+)
AND su_ship.cust_acct_site_id = raa_ship.cust_acct_site_id(+)
AND raa_ship.party_site_id = raa_ship_ps.party_site_id(+)
AND raa_ship_ps.location_id = raa_ship_loc.location_id(+)
AND raa_bill_loc.country = ftt_ship.territory_code
-------------------------------------------------------------------------------------------------
-- End of Ship to Address
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Item Description & Sales Details
-------------------------------------------------------------------------------------------------
AND rct.customer_trx_id = rctl.customer_trx_id
AND UPPER (rctl.line_type) IN ('LINE', 'CB')
AND rctl.inventory_item_id = msif.inventory_item_id(+)
AND rctl.warehouse_id = msif.organization_id(+)
-- AND (rctl.sales_order = rct.ct_reference OR rct.ct_reference is null)
AND rctl.line_number =
(SELECT MIN (rctl1.line_number)
FROM ra_customer_trx_lines rctl1
WHERE rctl.customer_trx_id = rctl1.customer_trx_id
AND UPPER (rctl1.line_type) IN ('LINE', 'CB')
GROUP BY rctl1.customer_trx_id)
-------------------------------------------------------------------------------------------------
-- End of Item Description & Sales Details
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Excise Invoice
-------------------------------------------------------------------------------------------------
AND rctl.customer_trx_id = jatl.customer_trx_id(+)
AND rctl.customer_trx_line_id = jatl.customer_trx_line_id(+)
AND rctl.inventory_item_id = jatl.inventory_item_id(+)
-------------------------------------------------------------------------------------------------
-- End of Excise Invoice
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Document Type
-------------------------------------------------------------------------------------------------
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
-------------------------------------------------------------------------------------------------
-- End of Document Type
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- GL Date
-------------------------------------------------------------------------------------------------
AND rct.customer_trx_id = gd.customer_trx_id
AND gd.account_class IN ('REC', 'REV')
AND 'Y' = gd.latest_rec_flag
-------------------------------------------------------------------------------------------------
-- End of GL Date
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Payment Terms
-------------------------------------------------------------------------------------------------
AND rct.term_id = rt.term_id(+)
-------------------------------------------------------------------------------------------------
-- End of Payment Terms
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
AND rctl.sales_order = ooh.order_number(+)
AND ooh.cust_po_number = ph.segment1(+)
AND rctl.interface_line_attribute6 = ool.line_id(+)
-------------------------------------------------------------------------------------------------
-- End of Customer PO details & Delivered Quantity
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Invoice Amount
-------------------------------------------------------------------------------------------------
AND rct.trx_number = jat.trx_number(+)
AND rct.customer_trx_id = jat.customer_trx_id(+)
-------------------------------------------------------------------------------------------------
-- End of Invoice Amount
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Inter-company
-------------------------------------------------------------------------------------------------
AND gd.code_combination_id = gcc.code_combination_id
AND gcc.segment7 = ffv.flex_value
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = 'DK_INTERCOMPANY'
-------------------------------------------------------------------------------------------------
-- End of Inter-company
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
AND rct.trx_number = aps.trx_number
AND rct.customer_trx_id = aps.customer_trx_id
-------------------------------------------------------------------------------------------------
-- End of Details of Balance Due & Buckets
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
AND to_char(wnd.delivery_id(+)) = rctl.interface_line_attribute3
-------------------------------------------------------------------------------------------------
-- End of Details of Delivery Id and Delivery date
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Report Parameters
-------------------------------------------------------------------------------------------------
&pl_customer_number
&Pl_COUNTRY
&pl_salesrepid
--&pl_include_fully_paid_invoices
&pl_due_date
&pl_invoice_date
&pl_gl_date_inv
-------------------------------------------------------------------------------------------------
-- End of Report Parameters
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Test Conditions
-------------------------------------------------------------------------------------------------
--and rct.TRX_NUMBER = 'DOI20100553'
--AND ac.customer_number = 1060
--and rownum < 1000
--and ooh.order_number = 2110010103
--AND rct.trx_number = '211000259'
--and aps.PAYMENT_SCHEDULE_ID IN( 1112,1113,1114,1115)
--and rct.customer_trx_id = 1110
--and aps.amount_applied is not null
-------------------------------------------------------------------------------------------------
-- End of Test Conditions
-------------------------------------------------------------------------------------------------
)
UNION
(SELECT
--distinct
acr.cash_receipt_id main_id,
aps.payment_schedule_id,
aps.CLASS,
acr.status,
ac.customer_number,
ac.customer_number customer_number1,
ac.customer_id,
NULL bill_to_customer_id,
ac.customer_name,
upper(ac.customer_name) customer_name2,
NULL trx_number,
NULL customer_trx_id,
NULL cust_acct_site_id,
NULL cust_acct_site_id,
NULL party_site_id,
NULL bill_to_address,
NULL bill_to_country_code,
NULL bill_to_country,
NULL ship_to_address,
NULL ship_to_country_code,
NULL ship_to_country,
NULL ship_to_site_use_id,
NULL site_use_id,
NULL item,
NULL item_description,
NULL delivery_date,
NULL delivery_id,
NULL org_id,
(select hpc.SUB_PERSON_PRE_NAME_ADJUNCT||' '||hpc.SUB_PERSON_FIRST_NAME||' '||hpc.SUB_PERSON_LAST_NAME
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_person,
(select hpc.REL_EMAIL_ADDRESS
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) mail_id,
(select hpc.REL_PHONE_COUNTRY_CODE||'-'||hpc.REL_PHONE_AREA_CODE||'-'||hpc.REL_PHONE_NUMBER
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_number,
NULL organization_id,
NULL excise_invoice_no,
NULL excise_invoice_date,
acr.receipt_number ar_invoice_no,
acr.receipt_date trx_date,
acr.exchange_rate,
TO_CHAR (TO_DATE (acr.receipt_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
ar_invoice_date,
TO_CHAR (TO_DATE (aps.due_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') due_date,
'Receipt' NAME,
TO_CHAR (TO_DATE (acrh.gl_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') gl_date,
NULL sales_order,
NULL sales_order_line_no,
NULL ci_no,
NULL ci_date,
NULL sales_order_type,
NULL payment_term,
NULL payment_term_description,
NULL cust_po_number,
NULL po_date1,
NULL po_date2,
NULL po_date,
NULL uom_code,
acr.currency_code currency,
acr.amount invoice_amount,
NULL segment7,
NULL intercompany,
-- ool.shipped_quantity,
-- SUM (ool.shipped_quantity) OVER (PARTITION BY ool.header_id)
-- total_shipped_quantity,
NULL total_shipped_quantity,
NULL fiscal_period,
NULL fiscal_year,
NULL c_form,
NULL trx_number,
NULL order_number,
aps.amount_due_original original_amount,
aps.amount_applied amount_recieved,
aps.amount_credited credit_refunds,
aps.amount_adjusted adjustments,
NULL assignment,
aps.discount_taken_earned discounts,
aps.amount_due_remaining bal_amt_in_entered_cur,
ac.primary_salesrep_id
FROM ar_customers ac,
hz_cust_accounts hca,
ar_cash_receipts acr,
ar_payment_schedules aps,
ar_cash_receipt_history_all acrh
WHERE ac.customer_id = hca.cust_account_id
AND acr.pay_from_customer = hca.cust_account_id
AND aps.cash_receipt_id = acr.cash_receipt_id
AND acr.cash_receipt_id = acrh.cash_receipt_id
AND acrh.first_posted_record_flag(+) = 'Y'
-- AND aps.amount_due_remaining <> 0
-- AND aps.amount_due_remaining IS NOT NULL
-- AND acr.status <> 'REV'
AND NOT EXISTS
(select acrh1.status
from
ar_cash_receipt_history_all acrh1
where acr.cash_receipt_id = acrh1.cash_receipt_id
and acrh1.status = 'REVERSED'
)
-------------------------------------------------------------------------------------------------
-- Report Parameters
-------------------------------------------------------------------------------------------------
&pl_customer_number
&pl_salesrepid
&pl_due_date
&pl_receipt_date
&pl_gl_date_rcpt
-------------------------------------------------------------------------------------------------
-- End of Report Parameters
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Test Conditions
-------------------------------------------------------------------------------------------------
--AND ac.customer_number = 1060
--and rownum < 1000
--and acr.RECEIPT_NUMBER in ('22222','100')
-------------------------------------------------------------------------------------------------
-- End of Test Conditions
-------------------------------------------------------------------------------------------------
)
UNION
(SELECT
--distinct
acr.cash_receipt_id main_id,
aps.payment_schedule_id,
aps.CLASS,
acrh.status,
ac.customer_number,
ac.customer_number customer_number1,
ac.customer_id,
NULL bill_to_customer_id,
ac.customer_name,
upper(ac.customer_name) customer_name2,
NULL trx_number,
NULL customer_trx_id,
NULL cust_acct_site_id,
NULL cust_acct_site_id,
NULL party_site_id,
NULL bill_to_address,
NULL bill_to_country_code,
NULL bill_to_country,
NULL ship_to_address,
NULL ship_to_country_code,
NULL ship_to_country,
NULL ship_to_site_use_id,
NULL site_use_id,
NULL item,
NULL item_description,
NULL delivery_date,
NULL delivery_id,
NULL org_id,
(select hpc.SUB_PERSON_PRE_NAME_ADJUNCT||' '||hpc.SUB_PERSON_FIRST_NAME||' '||hpc.SUB_PERSON_LAST_NAME
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_person,
(select hpc.REL_EMAIL_ADDRESS
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) mail_id,
(select hpc.REL_PHONE_COUNTRY_CODE||'-'||hpc.REL_PHONE_AREA_CODE||'-'||hpc.REL_PHONE_NUMBER
from CSC_HZ_PARTIES_CONT_PTS_REL_V hpc,
HZ_CUST_ACCOUNTS hca
where hpc.OBJ_PARTY_ID = hca.PARTY_ID--'6377'
and hca.ACCOUNT_NUMBER = ac.CUSTOMER_NUMBER
and hpc.REL_CONTACT_POINT_TYPE = 'PHONE'
and hpc.SUB_PERSON_LAST_NAME is not null
and rownum = 1) contact_number,
NULL organization_id,
NULL excise_invoice_no,
NULL excise_invoice_date,
acr.receipt_number ar_invoice_no,
acr.receipt_date trx_date,
acr.exchange_rate,
TO_CHAR (TO_DATE (acr.receipt_date, 'DD-MON-RRRR'), 'DD-MON-RRRR')
ar_invoice_date,
TO_CHAR (TO_DATE (aps.due_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') due_date,
'Receipt' NAME,
TO_CHAR (TO_DATE (acrh1.gl_date, 'DD-MON-RRRR'), 'DD-MON-RRRR') gl_date,
NULL sales_order,
NULL sales_order_line_no,
NULL ci_no,
NULL ci_date,
NULL sales_order_type,
NULL payment_term,
NULL payment_term_description,
NULL cust_po_number,
NULL po_date1,
NULL po_date2,
NULL po_date,
NULL uom_code,
acr.currency_code currency,
acr.amount invoice_amount,
NULL segment7,
NULL intercompany,
-- ool.shipped_quantity,
-- SUM (ool.shipped_quantity) OVER (PARTITION BY ool.header_id)
-- total_shipped_quantity,
NULL total_shipped_quantity,
NULL fiscal_period,
NULL fiscal_year,
NULL c_form,
NULL trx_number,
NULL order_number,
aps.amount_due_original original_amount,
aps.amount_applied amount_recieved,
aps.amount_credited credit_refunds,
aps.amount_adjusted adjustments,
NULL assignment,
aps.discount_taken_earned discounts,
aps.amount_due_remaining bal_amt_in_entered_cur,
ac.primary_salesrep_id
FROM ar_customers ac,
hz_cust_accounts hca,
ar_cash_receipts acr,
ar_cash_receipt_history_all acrh,
ar_cash_receipt_history_all acrh1,
ar_payment_schedules aps
WHERE ac.customer_id = hca.cust_account_id
AND acr.pay_from_customer = hca.cust_account_id
AND acr.cash_receipt_id = acrh.cash_receipt_id(+)
AND acrh.status = 'REVERSED'
AND acr.cash_receipt_id = acrh1.cash_receipt_id(+)
ANd acrh1.status = 'CLEARED'
AND aps.cash_receipt_id = acr.cash_receipt_id
AND acr.receipt_date <> acr.reversal_date
-------------------------------------------------------------------------------------------------
-- Report Parameters
-------------------------------------------------------------------------------------------------
&pl_customer_number
&pl_salesrepid
&pl_due_date
&pl_receipt_date
&pl_gl_date_rev_rcpt
-------------------------------------------------------------------------------------------------
-- End of Report Parameters
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Test Conditions
-------------------------------------------------------------------------------------------------
--AND ac.customer_number = 1060
--and rownum < 1000
--and acr.RECEIPT_NUMBER in ('22222','100')
-------------------------------------------------------------------------------------------------
-- End of Test Conditions
-------------------------------------------------------------------------------------------------
))
No comments:
Post a Comment