[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT (PDII.PROJFUNC_BILL_AMOUNT/PDII.INV_AMOUNT) F_amount ,(PDII.PROJECT_BILL_AMOUNT/PDII.INV_AMOUNT) P_amount,
PDII.draft_invoice_num Inv_num FROM
PA_PWP_CUSTOMER_SUMM PPCS , PA_DRAFT_INVOICE_ITEMS PDII
WHERE PPCS.draft_invoice_num = PDII.draft_invoice_num
AND PPCS.INV_LINE_AMOUNT = 0
and PDII.project_id = P_Project_Id
AND TRANSFER_STATUS_CODE = 'A'
and PDII.LINE_NUM =
(SELECT min(PDII2.LINE_NUM) from PA_DRAFT_INVOICE_ITEMS PDII2 where
PDII.draft_invoice_num = PDII2.draft_invoice_num AND
PDII2.project_id = P_Project_Id AND PDII2.INV_AMOUNT <> 0
group by PDII2.project_id,PDII2.draft_invoice_num);
DELETE
FROM PA_PWP_CUSTOMER_SUMM WHERE project_id = P_Project_Id ;
SELECT org_id
INTO l_org_id
FROM pa_implementations;
INSERT
INTO PA_PWP_CUSTOMER_SUMM
( ORG_ID
, PROJECT_ID
, draft_invoice_num
, RA_INVOICE_NUMBER
, DRAFT_INVOICE_NUM_CREDITED
, SYSTEM_REFERENCE
, TRANSFER_STATUS_CODE
, CUSTOMER_ID
, CUSTOMER_NAME
, CUSTOMER_NUMBER
, INVOICE_DATE
, INVOICE_STATUS
, INVOICE_CLASS
, AGREEMENT_NUM
, BILL_THROUGH_DATE
, PROJFUNC_INVTRANS_RATE_TYPE
, PROJFUNC_INVTRANS_RATE_DATE
, INV_CURRENCY_CODE
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
)
SELECT I.ORG_ID,I.PROJECT_ID /* Bug 12534894 */
, I.draft_invoice_num
, I.RA_INVOICE_NUMBER
, I.DRAFT_INVOICE_NUM_CREDITED
, I.SYStem_reference
, I.TRANSFER_STATUS_CODE
, I.CUSTOMER_ID
, C.CUSTOMER_NAME
, C.CUSTOMER_NUMBER
, I.INVOICE_DATE
, LK.MEANING INVOICE_STATUS_M
,(select LK3.MEANING FROM PA_LOOKUPS LK3 WHERE LK3.LOOKUP_TYPE = 'INVOICE_CLASS'
AND LK3.LOOKUP_CODE = DECODE(ORG_INV.CANCELED_FLAG, 'Y', 'CANCEL',
DECODE(I.WRITE_OFF_FLAG, 'Y', 'WRITE_OFF',
DECODE(I.concession_flag, 'Y', 'CONCESSION',
DECODE(NVL(I.DRAFT_INVOICE_NUM_CREDITED, 0), 0, 'INVOICE',
'CREDIT_MEMO'))))
AND LK3.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(LK3.START_DATE_ACTIVE, SYSDATE- 1))
AND TRUNC(NVL(LK3.END_DATE_ACTIVE, SYSDATE)))
, A.AGREEMENT_NUM
, I.BILL_THROUGH_DATE
, PA_MULTI_CURRENCY.GET_USER_CONVERSION_TYPE(I.PROJFUNC_INVTRANS_RATE_TYPE) -- Bug 8205105
, I.PROJFUNC_INVTRANS_RATE_DATE
, I.INV_CURRENCY_CODE
, l_user_id
, l_date
, l_user_id
, l_date
FROM PA_DRAFT_INVOICES_ALL I
, PA_DRAFT_INVOICES_ALL ORG_INV
, PA_CUSTOMERS_V C
,PA_LOOKUPS LK
,PA_AGREEMENTS_ALL A
WHERE I.AGREEMENT_ID = A.AGREEMENT_ID
and I.PROJECT_ID = P_Project_Id
AND C.CUSTOMER_ID = I.CUSTOMER_ID
AND C.CUSTOMER_ID = A.CUSTOMER_ID
AND ORG_INV.PROJECT_ID (+) = I.PROJECT_ID
AND ORG_INV.DRAFT_INVOICE_NUM (+) = I.DRAFT_INVOICE_NUM_CREDITED
AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
AND LK.LOOKUP_CODE = DECODE(I.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR',
DECODE(I.APPROVED_DATE, NULL, 'UNAPPROVED',
DECODE(I.RELEASED_DATE, NULL, 'APPROVED',
DECODE(I.TRANSFER_STATUS_CODE,
'P', 'RELEASED',
'X', 'REJECTED IN TRANSFER',
'T', 'TRANSFERRED',
'A', 'ACCEPTED',
'R', 'REJECTED' ) ) ) ) ;
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET
( PFC_BILL_AMOUNT -- Bug 7707807 re-ordered the columns
,PFC_LINE_AMOUNT
,PFC_OUTSTANDING_AMOUNT --Bug 8200941 Outstanding amount columns added in the query
,PC_BILL_AMOUNT
,PC_LINE_AMOUNT
,PC_OUTSTANDING_AMOUNT
,INV_BILL_AMOUNT
,INV_LINE_AMOUNT
,INV_OUTSTANDING_AMOUNT
)
=
(SELECT
SUM(PROJFUNC_BILL_AMOUNT) a
,SUM(PROJFUNC_BILL_AMOUNT) b
,SUM(PROJFUNC_BILL_AMOUNT)c
,SUM(PROJECT_BILL_AMOUNT)d
,SUM(PROJECT_BILL_AMOUNT)e
,SUM(PROJECT_BILL_AMOUNT)f
,SUM(INV_AMOUNT)g
,SUM(INV_AMOUNT)h
,SUM(INV_AMOUNT) i
FROM pa_draft_invoice_items pdii
WHERE pdii .project_id = pwp.project_id
AND pdii.draft_invoice_num = pwp.draft_invoice_num
)
WHERE project_id = P_Project_Id;
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET
(
INV_BILL_AMOUNT
, INV_OUTSTANDING_AMOUNT
, INV_RECIEPT_AMOUNT
, INV_ADJUSTMENT_AMOUNT
, INV_TAX_AMOUNT
)
=
(SELECT SUM(ARP.amount_due_original )
, SUM(ARP.amount_due_remaining)
, SUM(ARP.amount_applied)
, SUM(nvl(ARP.amount_credited,0)) + SUM(nvl(ARP.amount_adjusted,0)) -- Bug 7785173 Added NVL
, SUM(ARP.TAX_ORIGINAL) Tax
FROM AR_PAYMENT_SCHEDULES ARP
WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
)
WHERE project_id = P_Project_Id
AND TRANSFER_STATUS_CODE = 'A';
SELECT start_date
, end_date
INTO l_start_date
,l_end_date
FROM gl_period_statuses GL1
, pa_implementations pa
WHERE GL1.set_of_books_id = pa.set_of_books_id
AND GL1.APPLICATION_ID = 101 --bug 8208525
AND GL1.CLOSING_STATUS = 'O'
AND start_date =
(SELECT MAX(GL2.start_date)
FROM gl_period_statuses GL2
WHERE GL2.set_of_books_id = GL1.set_of_books_id
AND GL2.APPLICATION_ID = 101
AND GL2.CLOSING_STATUS = 'O'
);*/
SELECT GL1.start_date ,
GL1.end_date
INTO l_start_date ,
l_end_date
FROM gl_period_statuses GL1 ,
pa_implementations pa ,
pa_projects ppa
WHERE PPA.project_id = P_Project_Id
AND pa.org_id = ppa.org_id
AND GL1.set_of_books_id = pa.set_of_books_id
AND GL1.APPLICATION_ID = 101
AND GL1.CLOSING_STATUS = 'O'
AND GL1.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GL1.EFFECTIVE_PERIOD_NUM =
(SELECT MAX(GL2.EFFECTIVE_PERIOD_NUM)
FROM gl_period_statuses GL2 ,
pa_implementations pa2 ,
pa_projects ppa2
WHERE PPA2.project_id = P_Project_Id
AND pa2.org_id = ppa2.org_id
AND GL2.set_of_books_id = pa2.set_of_books_id
AND GL2.APPLICATION_ID = 101
AND GL2.CLOSING_STATUS = 'O'
AND GL2.ADJUSTMENT_PERIOD_FLAG = 'N'
);
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET
(
INV_CP_BILL_AMOUNT
,INV_CP_RECIEPT_AMOUNT
)
=
(SELECT SUM(ARP.amount_due_original )
, SUM(ARP.amount_applied)
FROM AR_PAYMENT_SCHEDULES ARP
WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
AND GL_DATE BETWEEN l_start_date AND l_end_date
)
WHERE project_id = P_Project_Id; */ -- bug 8208525 commented the code .
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET ( INV_CP_BILL_AMOUNT
,PFC_CP_BILL_AMOUNT
,PC_CP_BILL_AMOUNT)
=
(SELECT SUM(INV_AMOUNT ) -- bug 8225160
,SUM(PROJFUNC_BILL_AMOUNT)
,SUM(PROJECT_BILL_AMOUNT)
FROM pa_draft_invoice_items pdii,
pa_draft_invoices_all pda
WHERE pwp.project_id = pda.project_id
AND pwp.draft_invoice_num = pda.draft_invoice_num
AND pda.gl_date between l_start_date AND l_end_date
AND pdii.project_id = pwp.project_id
AND pdii.draft_invoice_num = pwp.draft_invoice_num
AND pda.project_id = pdii.project_id
)
WHERE PWP.project_id = P_Project_Id AND PWP.TRANSFER_STATUS_CODE <> 'A';
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET INV_CP_BILL_AMOUNT = (SELECT SUM(ARP.amount_due_original )
FROM AR_PAYMENT_SCHEDULES ARP,
PA_DRAFT_INVOICES PDA
WHERE PWP.SYSTEM_REFERENCE = ARP.CUSTOMER_TRX_ID
AND ARP.CUSTOMER_TRX_ID = PDA.SYSTEM_REFERENCE
AND PDA.GL_DATE BETWEEN l_start_date AND l_end_date
)
WHERE PWP.project_id = P_Project_Id AND PWP.TRANSFER_STATUS_CODE = 'A';
UPDATE PA_PWP_CUSTOMER_SUMM PWP
SET INV_CP_RECIEPT_AMOUNT =
(SELECT SUM(AMOUNT_APPLIED) FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
WHERE
PWP.SYSTEM_REFERENCE = ARA.APPLIED_CUSTOMER_TRX_ID
AND ARA.GL_DATE BETWEEN l_start_date AND l_end_date
)
WHERE PWP.PROJECT_ID = P_PROJECT_ID AND PWP.TRANSFER_STATUS_CODE = 'A';
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET (PROJFUNC_CURRENCY_CODE ,PROJECT_CURRENCY_CODE)
= (select PROJFUNC_CURRENCY_CODE, PROJECT_CURRENCY_CODE
from pa_projects_all pa where pwp.project_id = pa.project_id )
Where project_id = P_Project_Id;
UPDATE PA_PWP_CUSTOMER_SUMM
SET PFC_BILL_AMOUNT = INV_BILL_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_TAX_AMOUNT = INV_TAX_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PFC_LINE_AMOUNT = INV_LINE_AMOUNT * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_BILL_AMOUNT = INV_BILL_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_TAX_AMOUNT = INV_TAX_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
, PC_LINE_AMOUNT = INV_LINE_AMOUNT * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
WHERE project_id = P_project_id
AND TRANSFER_STATUS_CODE = 'A'
AND INV_LINE_AMOUNT <> 0;
UPDATE PA_PWP_CUSTOMER_SUMM
SET PFC_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0)) * (PFC_LINE_AMOUNT/ INV_LINE_AMOUNT)
,PC_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0)) * (PC_LINE_AMOUNT/ INV_LINE_AMOUNT)
,INV_OUTSTANDING_AMOUNT = (INV_BILL_AMOUNT - nvl(INV_RECIEPT_AMOUNT,0))
WHERE project_id = P_project_id ;
UPDATE PA_PWP_CUSTOMER_SUMM
SET PFC_BILL_AMOUNT = INV_BILL_AMOUNT * (Rec.F_amount)
, PFC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (Rec.F_amount)
, PFC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (Rec.F_amount)
, PFC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (Rec.F_amount)
, PFC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (Rec.F_amount)
, PFC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (Rec.F_amount)
, PFC_TAX_AMOUNT = INV_TAX_AMOUNT * (Rec.F_amount)
, PFC_LINE_AMOUNT = INV_LINE_AMOUNT * (Rec.F_amount)
, PC_BILL_AMOUNT = INV_BILL_AMOUNT * (Rec.P_amount)
, PC_CP_BILL_AMOUNT = INV_CP_BILL_AMOUNT * (Rec.P_amount)
, PC_RECIEPT_AMOUNT = INV_RECIEPT_AMOUNT * (Rec.P_amount)
, PC_CP_RECIEPT_AMOUNT = INV_CP_RECIEPT_AMOUNT * (Rec.P_amount)
, PC_OUTSTANDING_AMOUNT = INV_OUTSTANDING_AMOUNT * (Rec.P_amount)
, PC_ADJUSTMENT_AMOUNT = INV_ADJUSTMENT_AMOUNT * (Rec.P_amount)
, PC_TAX_AMOUNT = INV_TAX_AMOUNT * (Rec.P_amount)
, PC_LINE_AMOUNT = INV_LINE_AMOUNT * (Rec.P_amount)
WHERE project_id = P_project_id
AND TRANSFER_STATUS_CODE = 'A'
AND draft_invoice_num = Rec.Inv_num ;
UPDATE PA_PWP_CUSTOMER_SUMM
SET PFC_RECIEPT_AMOUNT = 0
,PFC_CP_RECIEPT_AMOUNT = 0
,PC_RECIEPT_AMOUNT = 0
,PC_CP_RECIEPT_AMOUNT = 0
,INV_RECIEPT_AMOUNT = 0
,INV_CP_RECIEPT_AMOUNT = 0
WHERE
DRAFT_INVOICE_NUM_CREDITED IS NOT NULL
AND PROJECT_ID = P_project_id ;
/*Bug#:7834036 sosharma added update the personalizable columns*/
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET (APPROVED_DATE
,GL_DATE
,RELEASED_DATE)
=
(SELECT pda.APPROVED_DATE
, pda.GL_DATE
, pda.RELEASED_DATE
FROM pa_draft_invoices_all pda
WHERE pwp.project_id = pda.project_id
AND pwp.draft_invoice_num = pda.draft_invoice_num
)
WHERE PWP.project_id = P_Project_Id ;
UPDATE PA_PWP_CUSTOMER_SUMM pwp
SET (BILL_TO_CUST_NUMBER,
BILL_TO_CUST_NAME,
SHIP_TO_CUST_NUMBER,
SHIP_TO_CUST_NAME
)
=
(select bill_c.customer_number,
bill_c.customer_name,
ship_c.customer_number,
ship_c.customer_name
from
pa_draft_invoices_all pda,
pa_customers_v bill_c,
pa_customers_v ship_c
where
bill_c.customer_id (+) = pda.bill_to_customer_id
AND ship_c.customer_id (+) = pda.ship_to_customer_id
AND pwp.project_id = pda.project_id
AND pwp.draft_invoice_num = pda.draft_invoice_num)
WHERE PWP.project_id = P_Project_Id ;
UPDATE PA_PWP_CUSTOMER_SUMM pwp
set(bill_to_address, ship_to_address) =
(select
hz_format_pub.format_address_lov(
bill_p.address1 ,
bill_p.address2,
bill_p.address3,
bill_p.address4 ,
bill_p.city ,
bill_p.postal_code ,
bill_p.state,
bill_p.province,
bill_p.county,
bill_p.country,
bill_p.address_lines_phonetic
) bill_to_address,
hz_format_pub.format_address_lov(
ship_p.address1 ,
ship_p.address2,
ship_p.address3,
ship_p.address4 ,
ship_p.city ,
ship_p.postal_code ,
ship_p.state,
ship_p.province,
ship_p.county,
ship_p.country,
ship_p.address_lines_phonetic
) ship_to_address
from
pa_draft_invoices_all pda,
RA_ADDRESSES_ALL bill_p,
RA_ADDRESSES_ALL ship_p
where
bill_p.address_id(+) = pda.bill_to_address_id
AND ship_p.address_id (+) = pda.ship_to_address_id
AND pwp.project_id = pda.project_id
AND pwp.draft_invoice_num = pda.draft_invoice_num)
WHERE PWP.project_id = P_Project_Id ;
UPDATE PA_PWP_CUSTOMER_SUMM_ALL pwp
SET
( bill_to_address,
ship_to_address
)
=
(SELECT ARP_ADDR_PKG.FORMAT_ADDRESS ( ba_loc.ADDRESS_STYLE ,ba_loc.ADDRESS1 ,ba_loc.ADDRESS2
,ba_loc.ADDRESS3 ,ba_loc.ADDRESS4 ,ba_loc.CITY ,ba_loc.COUNTY ,ba_loc.STATE ,
ba_loc.PROVINCE ,ba_loc.POSTAL_CODE ,TERR_B.TERRITORY_SHORT_NAME )
BILL_CONCATENATED_ADDRESS,
ARP_ADDR_PKG.FORMAT_ADDRESS ( sa_loc.ADDRESS_STYLE ,sa_loc.ADDRESS1 ,sa_loc.ADDRESS2
,sa_loc.ADDRESS3 ,sa_loc.ADDRESS4 ,sa_loc.CITY ,sa_loc.COUNTY ,sa_loc.STATE ,
sa_loc.PROVINCE ,sa_loc.POSTAL_CODE ,TERR_S.TERRITORY_SHORT_NAME )
SHIP_CONCATENATED_ADDRESS
FROM FND_TERRITORIES_TL TERR_B ,
FND_TERRITORIES_TL TERR_S ,
PA_DRAFT_INVOICES_ALL PDA ,
hz_cust_acct_sites sa_acct_site ,
hz_party_sites sa_party_site ,
hz_locations sa_loc ,
hz_locations ba_loc ,
hz_party_sites ba_party_site ,
hz_cust_acct_sites ba_acct_site
WHERE pda.project_id = pwp.project_id
AND pda.draft_invoice_num = pwp.draft_invoice_num
AND sa_loc.COUNTRY = TERR_S.TERRITORY_CODE(+)
AND ba_loc.COUNTRY = TERR_B.TERRITORY_CODE(+)
AND TERR_B.LANGUAGE =USERENV('LANG')
AND TERR_S.LANGUAGE =USERENV('LANG')
AND ba_acct_site.cust_acct_site_id(+) = pda.bill_to_address_id
AND ba_acct_site.party_site_id = ba_party_site.party_site_id(+)
AND ba_party_site.location_id = ba_loc.location_id(+)
AND sa_acct_site.cust_acct_site_id(+) = pda.Ship_to_address_id
AND sa_acct_site.party_site_id = sa_party_site.party_site_id (+)
AND sa_party_site.location_id = sa_loc.location_id(+)
)
WHERE PWP.project_id = P_Project_Id ;
SELECT ra_invoice_number
INTO l_invoice_num
FROM pa_draft_invoices
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
SELECT invoice_date
INTO L_INVOICE_DATE
FROM pa_draft_invoices
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;