[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR cur_inv_group_columns IS SELECT grp.column_code column_code,
fmtdet.text text,
fmtdet.start_position start_position,
fmtdet.end_position end_position,
NVL(fmtdet.right_justify_flag,'N') right_justify_flag
FROM pa_invoice_group_columns grp,
pa_invoice_formats fmt,
pa_invoice_format_details fmtdet,
pa_projects_all pr
WHERE pr.retn_billing_inv_format_id = fmt.invoice_format_id
AND fmt.invoice_format_id = fmtdet.invoice_format_id
AND grp.invoice_group_column_id = fmtdet.invoice_group_column_id
and pr.project_id =p_project_id
ORDER BY fmtdet.start_position;
SELECT 'Y'
INTO ExistsFlag
FROM DUAL
WHERE EXISTS(SELECT NULL
FROM pa_draft_invoices_all
WHERE project_id = p_project_id
AND agreement_id = p_agreement_id
AND request_id = p_request_id
AND NVL(retention_invoice_flag,'N') = 'Y');
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
select DISTINCT
nvl(cp1.credit_hold, cp.credit_hold),
to_char(c.customer_bill_split),
c.bill_to_address_id,
c.ship_to_address_id,
ras.site_use_id,
ras1.site_use_id,
addr.language,
a.agreement_currency_code,
pr.invoice_comment,
pr.retention_tax_code,
NVL(pr.inv_by_bill_trans_curr_flag ,'N'),
DECODE(pr.invproc_currency_type,
'PROJECT_CURRENCY',pr.project_currency_code,
'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
'FUNDING_CURRENCY', a.agreement_currency_code),
NVL(pr.retn_billing_inv_format_id,0),
c.customer_id,
c.bill_to_customer_id,
c.ship_to_customer_id
*/
select DISTINCT
DECODE(hz_cp1.credit_hold,NULL,hz_cp.credit_hold,'N',hz_cp.credit_hold,hz_cp1.credit_hold), /* Modified for bug 9251471 */
to_char(c.customer_bill_split),
c.bill_to_address_id,
c.ship_to_address_id,
hz_site.site_use_id,
hz_site1.site_use_id,
addr.language,
a.agreement_currency_code,
pr.invoice_comment,
pr.retention_tax_code,
NVL(pr.inv_by_bill_trans_curr_flag ,'N'),
DECODE(pr.invproc_currency_type,
'PROJECT_CURRENCY',pr.project_currency_code,
'PROJFUNC_CURRENCY',pr.projfunc_currency_code,
'FUNDING_CURRENCY', a.agreement_currency_code),
NVL(pr.retn_billing_inv_format_id,0),
c.customer_id,
c.bill_to_customer_id,
c.ship_to_customer_id,
a.payment_set_id
INTO
TmpCreditHold,
TmpCustBillSplit,
TmpBillToAddressID,
TmpShipToAddressID,
TmpSiteUSeId1,
TmpSiteUseId2,
TmpLanguage,
TmpFundingCurrency,
TmpInvoiceComment,
TmpRetnTaxCode,
TmpInvByBTC,
TmpInvProcCurrency,
TmpRetnBillInvFmtId,
TmpCustomerid,
TmpBilltocustomerid,
TmpShiptocustomerid,
TmpPaymentSetid
/* TCA changes
from ar_customer_profiles cp1,
ra_customers rc,
ra_customers rc1, --Added for customer account relation
ar_customer_profiles cp,
ra_site_uses ras,
pa_project_customers c,
pa_agreements_all a,
pa_projects pr,
ra_site_uses ras1,
ra_addresses addr
*/
from hz_customer_profiles hz_cp1,
hz_cust_accounts hz_c,
hz_cust_accounts hz_c1,
hz_customer_profiles hz_cp,
hz_cust_site_uses hz_site,
pa_project_customers c,
pa_agreements_all a,
pa_projects pr,
hz_cust_site_uses hz_site1,
hz_cust_acct_sites addr
where a.agreement_id = p_agreement_id
and pr.project_id = p_project_id
and pr.project_id = c.project_id
and a.customer_id = c.customer_id
/* and c.customer_id = cp.customer_id commented for customer account relation enhancement*/
/* TCA changes
and c.bill_to_customer_id = cp.customer_id
and c.bill_to_customer_id = rc1.customer_id
and nvl(rc1.status,'A') = 'A'
*/
and c.bill_to_customer_id = hz_cp.cust_account_id
and c.bill_to_customer_id = hz_c1.cust_account_id
and nvl(hz_c1.status,'A') = 'A'
/*End of change for customer account relation enhancement*/
/* TCA changes
and c.customer_id = rc.customer_id
*/
and c.customer_id = hz_c.cust_account_id
-- and c.customer_bill_split <> 0 -- commented for FP_M Changes
and Decode( pr.Enable_Top_Task_Customer_Flag, 'Y', 100,
decode(pr.date_eff_funds_consumption, 'Y', 100, c.customer_bill_split )) <> 0 -- FP_M changes
/* TCA changes
and nvl(rc.status,'A') = 'A'
and cp.site_use_id is null
and ras.address_id = c.bill_to_address_id
and ras.site_use_code = 'BILL_TO'
and ras.status = 'A'
and ras1.address_id = c.ship_to_address_id
and ras1.site_use_code = 'SHIP_TO'
and ras1.status = 'A'
and addr.address_id = c.bill_to_address_id
and cp1.site_use_id(+) = ras.site_use_id
*/
and nvl(hz_c.status,'A') = 'A'
and hz_cp.site_use_id is null
and hz_site.cust_acct_site_id = c.bill_to_address_id
and hz_site.site_use_code = 'BILL_TO'
and hz_site.status = 'A'
and hz_site1.cust_acct_site_id = c.ship_to_address_id
and hz_site1.site_use_code = 'SHIP_TO'
and hz_site1.status = 'A'
and addr.cust_acct_site_id = c.bill_to_address_id
and hz_cp1.site_use_id(+) = hz_site.site_use_id
/*Added for customer account relation enhancement bug no 2760630*/
and NOT EXISTS
(
/* Removed the existing code for perf bug 3607384 and added the below */
SELECT NULL
FROM PA_IMPLEMENTATIONS I
WHERE I.CUST_ACC_REL_CODE = 'Y'
AND exists ( select 1 from HZ_CUST_ACCT_RELATE HZ1,
PA_PROJECT_CUSTOMERS C
where C.PROJECT_ID=p_project_id
AND ( HZ1.CUST_ACCOUNT_ID(+) = C.CUSTOMER_ID
AND HZ1.RELATED_CUST_ACCOUNT_ID(+) = C.BILL_TO_CUSTOMER_ID
AND (NVL(HZ1.STATUS,'A') <>'A'
OR NVL(HZ1.BILL_TO_FLAG,'Y') <>'Y')
AND C.CUSTOMER_ID <> C.BILL_TO_CUSTOMER_ID
))
UNION ALL
SELECT NULL
FROM PA_IMPLEMENTATIONS I
WHERE I.CUST_ACC_REL_CODE = 'Y'
AND exists ( select 1 from HZ_CUST_ACCT_RELATE HZ1,
PA_PROJECT_CUSTOMERS C
where C.PROJECT_ID=p_project_id
AND ( HZ1.CUST_ACCOUNT_ID(+) = C.CUSTOMER_ID
AND HZ1.RELATED_CUST_ACCOUNT_ID(+) = C.SHIP_TO_CUSTOMER_ID
AND (NVL(HZ1.STATUS,'A') <>'A'
OR NVL(HZ1.SHIP_TO_FLAG,'Y') <>'Y')
AND C.CUSTOMER_ID <> C.SHIP_TO_CUSTOMER_ID
))
UNION ALL
SELECT NULL
FROM PA_IMPLEMENTATIONS I
WHERE I.cust_acc_rel_code = 'N'
AND exists (select 1 from PA_PROJECT_CUSTOMERS C
WHERE C.PROJECT_ID = p_project_id
AND ( C.CUSTOMER_ID <> C.BILL_TO_CUSTOMER_ID
OR C.CUSTOMER_ID<>C.SHIP_TO_CUSTOMER_ID))
);
SELECT MIN(PROJCON.Contact_ID),
decode(MAX(decode(ROLE.Primary_Flag, 'Y', CONT.Contact_ID, -1)),
-1, decode(MIN(CONT.Contact_ID), 0, NULL, MIN(CONT.Contact_ID)),
MAX(decode(ROLE.Primary_Flag, 'Y', CONT.Contact_ID, -1)))
INTO TmpBilltocontactid,
TmpShiptocontactid
FROM pa_project_contacts projcon,
pa_project_contacts cont,
pa_project_customers c,
hz_role_responsibility role,
/* TCA changes
ra_contact_roles role,
*/
pa_agreements_all a /*Added for bug2984282*/
WHERE c.project_id=p_project_id
and a.agreement_id=p_agreement_id
and c.customer_id=p_customer_id/*Added for bug 2984282*/
and projcon.project_contact_type_code = 'BILLING'
and projcon.customer_id =c.customer_id
and projcon.project_ID = c.project_id
and cont.project_ID (+) = c.project_id
and cont.customer_ID (+) = c.customer_id
and cont.project_Contact_Type_Code (+) = 'SHIPPING'
/* TCA changes
and role.cust_account_role_id (+) = CONT.Contact_ID
and role.responsibility_type (+) = 'SHIP_TO'
*/
and role.cust_account_role_id (+) = CONT.Contact_ID
and role.responsibility_type (+) = 'SHIP_TO'
and NOT EXISTS (SELECT NULL
FROM PA_PROJECT_CUSTOMERS c1
WHERE c1.project_id=p_project_id
AND NOT EXISTS
(
SELECT NULL
FROM pa_project_contacts projcon
WHERE projcon.project_contact_type_code = 'BILLING'
AND projcon.customer_id =c1.customer_id
AND projcon.project_ID = c1.project_id)
);
SELECT PA_DRAFT_INVOICES_S.NEXTVAL
INTO TmpInvoiceSetId
FROM DUAL;
SELECT NVL( MAX(p.draft_invoice_num) + 1, 1)
INTO TmpInvoiceNum
FROM pa_draft_invoices_all p
WHERE p.project_id = p_project_id;
SELECT imp.set_of_books_id
INTO TmpSetOfBooks
FROM pa_implementations imp;
pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Insert into Invoice Header ');
-- If the customer is on credit-hold, insert a warning
-- TmpWarningMsg :='Customer has been put on billing hold.
-- Invoice cannot be generated.';
SELECT lk.meaning
INTO TmpWarningMsg
FROM pa_lookups lk
WHERE lk.lookup_code = TmpWarningCode
AND lk.lookup_type = 'INVOICE DISTRIBUTION WARNING';
insert it into table pa_draft_invoices as ORG_ID. */
l_org_id := MO_GLOBAL.get_current_org_id;
-- Insert a new invoice header
/*Last 5 columns added for customer account relation enhancement bug no 2760630*/
INSERT INTO PA_DRAFT_INVOICES (
DRAFT_INVOICE_NUM, PROJECT_ID,
AGREEMENT_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, TRANSFER_STATUS_CODE,
GENERATION_ERROR_FLAG, PA_DATE,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, Program_Update_Date,
BILL_THROUGH_DATE, TRANSFER_REJECTION_REASON,
RETENTION_PERCENTAGE, Unearned_Revenue_CR,
Unbilled_Receivable_DR,
-- Invoice_Set_ID,
DRAFT_INVOICE_NUM_CREDITED, CUSTOMER_BILL_SPLIT,
INVOICE_COMMENT, INV_CURRENCY_CODE,
INV_RATE_TYPE,INV_RATE_DATE,INV_EXCHANGE_RATE,
BILL_TO_ADDRESS_ID,SHIP_TO_ADDRESS_ID,
LANGUAGE, INVPROC_CURRENCY_CODE,
INVOICE_DATE, GL_DATE,
PA_PERIOD_NAME,GL_PERIOD_NAME,
RETENTION_INVOICE_FLAG,
CUSTOMER_ID,BILL_TO_CUSTOMER_ID,SHIP_TO_CUSTOMER_ID,
BILL_TO_CONTACT_ID,SHIP_TO_CONTACT_ID,
ORG_ID, payment_set_id
) VALUES
(TmpInvoiceNum, p_project_id,
p_agreement_id, SYSDATE,
TmpUserId, SYSDATE,
TmpUserId, 'P',
DECODE(TmpCreditHold,'Y','Y','N',DECODE(TmpRetnBillInvFmtId,0,'Y','N')),
pa_billing.GetPaDate,
p_request_id,
TmpProgApplId,
TmpProgId, SYSDATE,
TO_DATE(pa_billing.GetBillThruDate, 'YYYY/MM/DD'),
/*DECODE(TmpCreditHold,'Y','Y','N',DECODE(TmpRetnBillInvFmtId,0,
TmpWarningMsg,Null)),*/
DECODE(TmpCreditHold,'Y',TmpWarningMsg,'N',DECODE(TmpRetnBillInvFmtId,0, TmpWarningMsg,Null)),
null, NULL,
NULL,
-- TmpInvoiceSetId,
NULL, TmpCustBillSplit,
TmpInvoiceComment,
TmpInvCurrency,
TmpInvCurrRateType,TmpInvCurrRateDate,
TmpInvCurrRate,TmpBillToAddressID,
TmpShipToAddressID,
TmpLanguage,
NVL(TmpInvProcCurrency,TmpFundingCurrency),
TRUNC(TmpInvoiceDate),
--TRUNC(TO_DATE(TmpInvoiceDate, 'YYYY/MM/DD')),
pa_billing.GetGlDate,
pa_billing.getpaperiodname,
pa_billing.getglperiodname,
'Y',
TmpCustomerid,
TmpBilltocustomerid,
TmpShiptocustomerid,
TmpBilltocontactid,
TmpShiptocontactid,
l_org_id,
TmpPaymentSetid);
pa_retention_util.write_log('Build_Retn_Invoice_Header: ' || 'Insert Warning ');
INSERT INTO PA_DISTRIBUTION_WARNINGS (
DRAFT_INVOICE_NUM, PROJECT_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE,
WARNING_MESSAGE, WARNING_MESSAGE_CODE)
VALUES
( TmpInvoiceNum, p_project_id,
SYSDATE, TmpUserId, SYSDATE,
TmpUserId, p_request_id, TmpProgApplId,
TmpProgId, SYSDATE,
TmpWarningMsg, TmpWarningCode);
-- This insert is for project level rejection reason. This will be shown
-- in the invoice exception report
IF NVL(TmpRetnBillInvFmtID,0) = 0 THEN
INSERT INTO PA_DISTRIBUTION_WARNINGS (
DRAFT_INVOICE_NUM, PROJECT_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE,
WARNING_MESSAGE, WARNING_MESSAGE_CODE)
VALUES
( null, p_project_id,
SYSDATE, TmpUserId, SYSDATE,
TmpUserId, p_request_id, TmpProgApplId,
TmpProgId, SYSDATE,
TmpWarningMsg, TmpWarningCode);
PROCEDURE Update_ProjFunc_Attributes( p_project_id IN NUMBER,
p_draft_invoice_num IN NUMBER) IS
l_projfunc_invtrans_rate NUMBER:=0;
SELECT NVL(sum(dii.inv_amount),0),
NVL(sum(dii.projfunc_bill_amount),0)
INTO l_inv_amount,
l_pfc_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_invoice_num;
UPDATE pa_draft_invoices_all
set inv_rate_date = NULL,
inv_exchange_rate = NULL,
projfunc_invtrans_rate_type = 'User',
projfunc_invtrans_rate_date = sysdate,
projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
WHERE project_id = P_Project_Id
AND draft_invoice_num = p_draft_invoice_num;
pa_retention_util.write_log('pa_retn_billing_pkg.Update_ProjFunc_Attributes' || ' Oracle Error : ' || sqlerrm);
END Update_ProjFunc_Attributes;
LastUpdatedBy NUMBER:= fnd_global.user_id;
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
SELECT LTRIM(RTRIM(task_name)) || ' ' INTO l_task_name FROM pa_tasks
WHERE task_id = p_task_id;
pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Insert DII');
INSERT INTO pa_draft_invoice_items
( PROJECT_ID,
DRAFT_INVOICE_NUM,
LINE_NUM,
AMOUNT,
TEXT,
INVOICE_LINE_TYPE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_BILL_AMOUNT,
PROJECT_CURRENCY_CODE,
PROJECT_BILL_AMOUNT,
FUNDING_CURRENCY_CODE,
FUNDING_BILL_AMOUNT,
INVPROC_CURRENCY_CODE,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OUTPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_EXEMPT_FLAG,
/* Bug 3087998 Code and number order is different in values list. Changing here to match the same
OUTPUT_TAX_EXEMPT_REASON_CODE,
OUTPUT_TAX_EXEMPT_NUMBER,
*/
OUTPUT_TAX_EXEMPT_NUMBER,
OUTPUT_TAX_EXEMPT_REASON_CODE,
INV_AMOUNT,
RETN_BILLING_METHOD,
RETN_PERCENT_COMPLETE,
RETN_TOTAL_RETENTION,
RETN_CLIENT_EXTENSION_FLAG,
RETN_BILLING_CYCLE_ID,
RETN_BILLING_PERCENTAGE,
RETN_BILLING_AMOUNT,
task_id)
VALUES (p_project_id,
p_draft_invoice_num,
LastLineNum,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_invproc_amount,p_invproc_currency),
SUBSTR(l_task_name || SUBSTR(RetnLineText,1,LastEndPosition),1,240),
'RETENTION',
p_projfunc_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_projfunc_amount,p_projfunc_currency),
p_project_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_project_amount,p_project_currency),
p_funding_currency,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_funding_amount,p_funding_currency),
p_invproc_currency,
l_last_update_login,
sysdate,
l_last_updated_by,
sysdate,
l_created_by,
p_request_id,
l_program_application_id,
l_program_id,
sysdate,
p_Output_tax_code,
p_Output_tax_exempt_flag ,
p_Output_tax_exempt_number ,
p_Output_exempt_reason_code,
DECODE(pa_retn_billing_pkg.G_Inv_By_Bill_Trans_Currency,
'Y',
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(p_invproc_amount,
p_invproc_currency),
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
p_projfunc_amount,p_projfunc_currency)),
p_billing_method_code,
p_comp_percent,
p_TotRetenion,
p_client_extn_flag,
p_bill_cycle_id,
p_billing_percentage,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(
p_billing_amount,
p_invproc_currency),
p_task_id);
-- Update the balances
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Create_Retn_Invoice_Lines: ' || 'Call pa_retention_pkg.Update_Retention_Balances');
pa_retention_pkg.Update_Retention_Balances(
p_project_id=>p_project_id,
p_agreement_id=>p_agreement_id,
p_task_id=>p_task_id,
p_customer_id=>p_customer_id,
p_amount =>p_invproc_amount,
p_change_type =>'BILLED',
p_request_id =>p_request_id,
p_invproc_currency=>p_invproc_currency,
p_project_currency=>p_project_currency,
p_project_amount =>p_project_amount,
p_projfunc_currency =>p_projfunc_currency,
p_projfunc_amount =>p_projfunc_amount,
p_funding_currency =>p_funding_currency,
p_funding_amount =>p_funding_amount);
SELECT
project_retention_id,
project_id,
task_id,
invproc_currency_code,
total_retained,
projfunc_currency_code,
projfunc_total_retained,
project_currency_code,
project_total_retained,
funding_currency_code,
funding_total_retained
FROM pa_project_retentions
WHERE project_id = p_project_id
AND agreement_id = p_agreement_id
AND nvl(task_id,-99) = NVL(p_task_id,-99);
LastUpdatedBy NUMBER:= fnd_global.user_id;
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
-- Select the sequence values
SELECT pa_retn_invoice_details_s.NEXTVAL
INTO l_detail_id
FROM DUAL;
pa_retention_util.write_log('Create_Retn_Invoice_Details: ' || 'Insert pa_retn_invoice_details');
-- Insert into the Retention Invoice Detail table
INSERT INTO pa_retn_invoice_details
( RETN_INVOICE_DETAIL_ID,
PROJECT_ID,
DRAFT_INVOICE_NUM,
LINE_NUM,
PROJECT_RETENTION_ID,
TOTAL_RETAINED,
INVPROC_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_TOTAL_RETAINED,
PROJECT_CURRENCY_CODE,
PROJECT_TOTAL_RETAINED,
FUNDING_CURRENCY_CODE,
FUNDING_TOTAL_RETAINED,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES( l_detail_id,
p_project_id,
p_draft_invoice_num,
p_line_num,
ProjRetnRec.project_retention_id,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.total_retained,
ProjRetnRec.invproc_currency_code),
ProjRetnRec.invproc_currency_code,
ProjRetnRec.projfunc_currency_code,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.projfunc_total_retained,
ProjRetnRec.projfunc_currency_code),
ProjRetnRec.project_currency_code,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.project_total_retained,
ProjRetnRec.project_currency_code),
ProjRetnRec.funding_currency_code,
PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(ProjRetnRec.funding_total_retained,
ProjRetnRec.funding_currency_code),
l_program_application_id,
l_program_id,
l_program_update_date,
p_request_id,
sysdate,
l_created_by,
l_last_update_date,
LastUpdatedBy);
CURSOR cur_proj_cust_retn IS SELECT pc.project_id project_id,
pc.customer_id customer_id,
pc.retention_level_code retention_level,
NVL(pr.RETN_BILLING_INV_FORMAT_ID,0) RETN_BILLING_INV_FORMAT_ID
/* TCA changes
FROM pa_project_customers pc, pa_projects_all pr, ra_customers c
*/
FROM pa_project_customers pc, pa_projects_all pr, hz_parties hz_p, hz_cust_accounts hz_c
WHERE EXISTS(
SELECT NULL FROM pa_summary_project_retn spr,
pa_proj_retn_bill_rules rt
WHERE rt.project_id = spr.project_id
AND rt.customer_id = spr.customer_id
AND spr.customer_id = pc.customer_id
AND spr.project_id = pc.project_id
AND (NVL(spr.total_retained,0) - NVL(spr.total_billed,0) ) > 0)
AND pr.project_id = pc.project_id
/* TCA changes
AND pc.customer_id = c.customer_id
*/
AND pc.customer_id = hz_c.cust_account_id
AND hz_p.party_id = hz_c.party_id
AND pr.segment1 between p_start_proj_number and p_end_proj_number
/*
AND ( nvl (p_agreement_id,0) = 0
OR EXISTS (select null
from pa_summary_project_fundings SPF
where spf.project_id = pr.project_id
and spf.agreement_id = p_agreement_id
and spf.total_baselined_amount > 0)
)
AND ( nvl(p_customer_id,0) = 0
OR pc.customer_id = p_customer_id
)
AND (nvl(p_project_type_id, 0) = 0
OR EXISTS ( select null
from pa_project_types pt
where pr.project_type = pt.project_type
and pt.project_type_id = p_project_type_id)
)
AND ( nvl(p_project_org_id,0) = 0
OR pr.carrying_out_organization_id = p_project_org_id
)
AND (p_mcb_flag = 'N'
OR nvl(pr.multi_currency_billing_flag, 'N') = 'Y')
*/
/* TCA changes
order by pr.segment1, c.customer_name;
CURSOR cur_retn_billing_rules IS SELECT retn1.billing_method_code billing_method,
retn1.retn_billing_percentage bill_percentage,
retn1.retn_billing_amount bill_amount,
retn1.total_retention_amount total_retention_amount,
retn1.retn_billing_cycle_id billing_cycle_id,
retn1.completed_percentage completed_percentage,
retn1.client_extension_flag client_extn_flag,
cy.billing_cycle_name billing_cycle_name,
lk.meaning billingMethodDesc,
retn1.task_id task_id
FROM pa_proj_retn_bill_rules retn1,
pa_billing_cycles cy,
pa_lookups lk
WHERE retn1.project_id = TmpProjectId
AND retn1.customer_id= TmpCustomerId
AND lk.lookup_code = retn1.billing_method_code
AND lk.lookup_type ='RETN_BILLING_METHOD'
AND retn1.billing_method_code <> 'PERCENT_COMPLETE'
AND retn1.retn_billing_cycle_id = cy.billing_cycle_id(+)
UNION
SELECT
retn.billing_method_code billing_method,
retn.retn_billing_percentage bill_percentage,
retn.retn_billing_amount bill_amount,
retn.total_retention_amount total_retention_amount ,
retn.retn_billing_cycle_id billing_cycle_id,
retn.completed_percentage completed_percentage,
retn.client_extension_flag client_extn_flag,
'NULL' billing_cycle_name,
lk.meaning billingMethodDesc,
retn.task_id task_id
FROM pa_proj_retn_bill_rules retn,
pa_lookups lk
WHERE retn.project_id = TmpProjectId
AND retn.customer_id= TmpCustomerId
AND lk.lookup_code = retn.billing_method_code
AND lk.lookup_type ='RETN_BILLING_METHOD'
AND retn.billing_method_code = 'PERCENT_COMPLETE'
AND
retn.completed_percentage <=
pa_bill_pct.GetPercentComplete(
retn.project_id, retn.task_id,TmpBillThruDate) /*Bug 3258465 Modified the call by passing TmpBillThruDate instead of TmpInvoiceDate*/
ORDER BY completed_percentage DESC;
CURSOR cur_agr IS SELECT
spr.agreement_id,
spr.project_id,
spr.task_id,
spr.invproc_currency_code,
spr.total_retained,
spr.total_billed,
spr.projfunc_currency_code,
spr.projfunc_total_retained,
spr.projfunc_total_billed,
spr.project_currency_code,
spr.project_total_retained,
spr.project_total_billed,
spr.funding_currency_code,
spr.funding_total_retained,
(NVL(spr.total_retained,0)- NVL(spr.total_billed,0)) Remain_Retained_Amt,
pr.inv_by_bill_trans_curr_flag inv_by_bill_trans_curr_flag
FROM pa_summary_project_retn spr,
pa_agreements_all agr,
pa_projects_all pr
WHERE agr.agreement_id = spr.agreement_id
AND agr.customer_id = TmpCustomerID
AND spr.project_id = TmpProjectId
AND spr.project_id = pr.project_id
AND NVL(spr.task_id,-99) = NVL(NewTask,-99)
AND NVL(spr.total_retained,0) <> 0 /*For Bug 7612216*/
ORDER BY DECODE(agr.invoice_limit_flag,'Y',1,2), agr.expiration_date;
SELECT SUM(NVL(spr.total_retained,0)) -
SUM(NVL(spr.total_billed,0))
INTO CurRetained
FROM pa_summary_project_retn spr
WHERE spr.project_id= TmpProjectId
AND spr.customer_id= TmpCustomerId; /*Added for bug 3234999*/
SELECT SUM(NVL(spr.total_retained,0)) -
SUM(NVL(spr.total_billed,0))
INTO CurRetained
FROM pa_summary_project_retn spr
WHERE spr.project_id= TmpProjectId
AND spr.task_id = NewTask
AND spr.customer_id= TmpCustomerId; /*Added for bug 3234999*/
p_action=>'INSERT',
p_request_id=>p_request_id);
-- Update only for invoice by bill transaction invoice
IF (SprRec.invproc_currency_code <> SprRec.projfunc_currency_code)
AND (SprRec.inv_by_bill_trans_curr_flag ='Y') THEN
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll Update_ProjFunc_Attributes');
Update_ProjFunc_Attributes(p_project_id=>TmpProjectId,
p_draft_invoice_num=>TmpInvoiceNum);
-- Update the invoice currency attributes for retention invoices
IF g1_debug_mode = 'Y' THEN
pa_retention_util.write_log('Retention_Billing_Processing: ' || 'CAll Update_Inv_Trans_Attributes');
Update_Inv_Trans_Attributes(p_request_id=>p_request_id);
pa_retention_util.write_log('Retention_Billing_Processing: ' || 'Update MRC for Retention Invoices');
UPDATE PA_DRAFT_INVOICES
SET CREATION_DATE = sysdate
WHERE REQUEST_ID = p_request_id
AND NVL(retention_invoice_flag,'N') = 'Y';
PROCEDURE Update_Inv_Trans_Attributes (p_request_id IN NUMBER) IS
CURSOR Retn_Inv_Project IS
SELECT project_id
FROM pa_draft_invoices
WHERE request_id = p_request_id
AND NVL(GENERATION_ERROR_FLAG,'N') ='N'
AND NVL(retention_invoice_flag,'N') = 'Y'
GROUP BY project_id;
pa_retention_util.write_log('Entering Update_Inv_Trans_Attributes');
pa_retention_util.write_log('Update_Inv_Trans_Attributes: ' || 'Calling PA_INVOICE_CURRENCY.Recalculate_Driver for project id : '
|| ProjectRec.project_id);
END Update_Inv_Trans_Attributes;
CURSOR cur_select_projects IS
SELECT pr.project_id project_id
FROM pa_projects pr, pa_project_types t
WHERE NOT EXISTS (SELECT null
FROM pa_draft_invoices_all di
WHERE di.request_id = p_request_id
AND di.project_id = pr.project_id
AND NVL(di.retention_invoice_flag,'N') = 'Y')
AND EXISTS( SELECT NULL
FROM pa_proj_retn_rules rt
WHERE rt.project_id = pr.project_id)
AND PA_Project_Utils.Check_prj_stus_action_allowed(pr.Project_Status_Code, 'GENERATE_INV') = 'Y'
AND pr.project_type = t.project_type
AND t.Project_type_class_code = 'CONTRACT'
AND pr.segment1 between p_start_proj_number and p_end_proj_number
ORDER BY pr.segment1;
RecSelectProjects cur_select_projects%ROWTYPE;
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
SELECT lk.meaning
INTO TmpWarningMsg
FROM pa_lookups lk
WHERE lk.lookup_code = TmpWarningCode
AND lk.lookup_type = 'INVOICE DISTRIBUTION WARNING';
OPEN cur_select_projects;
FETCH cur_select_projects INTO RecSelectProjects;
EXIT WHEN cur_select_projects%NOTFOUND;
pa_retention_util.write_log('Invoice_Generation_Exceptions: ' || 'Insert Warning ');
INSERT INTO PA_DISTRIBUTION_WARNINGS (
DRAFT_INVOICE_NUM, PROJECT_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
REQUEST_ID, PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE,
WARNING_MESSAGE, WARNING_MESSAGE_CODE)
VALUES
( TmpInvoiceNum, RecSelectProjects.project_id,
SYSDATE, TmpUserId, SYSDATE,
TmpUserId, p_request_id, TmpProgApplId,
TmpProgId, SYSDATE,
TmpWarningMsg, TmpWarningCode);
CLOSE cur_select_projects;