Showing posts with label Oracle Receivable. Show all posts
Showing posts with label Oracle Receivable. Show all posts
Saturday, September 19, 2015
Monday, August 03, 2015
Customer Contact Info from Header Level.
Customer Contact Info from Header Level.
select (select party_name from hz_parties where party_id=hca.party_id) customer_name,
hca.account_number,
hp.person_last_name
, hoc.contact_number,
hp1.email_address
FROM hz_cust_account_roles hcar
, hz_parties hp
, hz_relationships hr
, hz_org_contacts hoc
, hz_parties hp1
, hz_cust_accounts hca
WHERE hcar.party_id = hr.party_id
AND hcar.role_type = 'CONTACT'
AND hoc.party_relationship_id= hr.relationship_id
AND hr.subject_id = hp.party_id
AND hr.party_id = hp1.party_id
AND hr.subject_table_name = 'HZ_PARTIES'
AND hr.object_table_name = 'HZ_PARTIES'
AND hcar.cust_account_id = hca.cust_account_id
AND hca.party_id = hr.object_id
-- AND hcar.cust_account_role_id = v_ship_contact_id;
order by 2
select (select party_name from hz_parties where party_id=hca.party_id) customer_name,
hca.account_number,
hp.person_last_name
, hoc.contact_number,
hp1.email_address
FROM hz_cust_account_roles hcar
, hz_parties hp
, hz_relationships hr
, hz_org_contacts hoc
, hz_parties hp1
, hz_cust_accounts hca
WHERE hcar.party_id = hr.party_id
AND hcar.role_type = 'CONTACT'
AND hoc.party_relationship_id= hr.relationship_id
AND hr.subject_id = hp.party_id
AND hr.party_id = hp1.party_id
AND hr.subject_table_name = 'HZ_PARTIES'
AND hr.object_table_name = 'HZ_PARTIES'
AND hcar.cust_account_id = hca.cust_account_id
AND hca.party_id = hr.object_id
-- AND hcar.cust_account_role_id = v_ship_contact_id;
order by 2
Wednesday, July 22, 2015
Customer wise AR Aging
((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
-------------------------------------------------------------------------------------------------
))
Subscribe to:
Posts (Atom)
Clear BNE Cache for WebADI Changes
It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...
