[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT FC.Minimum_Accountable_Unit,
FC.Precision,
FC.Extended_Precision
INTO l_mau,
l_sp,
l_ep
FROM FND_CURRENCIES FC
WHERE FC.Currency_Code = l_curr_code;
insert proper distribution entries in RA interface distribution. Then
perform the update of invoice line with the computed line amount,Write-
off/Revenue Rounding amt in accounting currency.
**/
PROCEDURE Cr_Single_RND_Entries ( P_Batch_Src IN VARCHAR2,
P_Interface_attr1 IN VARCHAR2,
P_Interface_attr2 IN VARCHAR2,
P_Interface_attr3 IN VARCHAR2,
P_Interface_attr4 IN VARCHAR2,
P_Interface_attr5 IN VARCHAR2,
P_Interface_attr6 IN VARCHAR2,
P_Interface_attr7 IN VARCHAR2,
P_Interface_attr8 IN VARCHAR2,
P_Func_currency_code IN VARCHAR2,
P_Inv_currency_code IN VARCHAR2,
P_Single_Acct_Ccid IN NUMBER,
P_RND_ccid IN NUMBER,
P_Inv_line_amt IN NUMBER,
P_Proj_line_amt IN NUMBER,
P_Project_id IN NUMBER,
P_Conv_rate IN NUMBER,
P_Draft_inv_num IN NUMBER,
X_Acct_amt OUT NOCOPY NUMBER ) --File.Sql.39 bug 4440895
AS
l_rate NUMBER;
/** Update the lines Acct amount same as project currency amount **/
UPDATE PA_DRAFT_INVOICE_ITEMS
/* MCB2 change
SET ACCT_AMOUNT = AMOUNT,
*/
SET ACCT_AMOUNT = PROJFUNC_BILL_AMOUNT,
ROUNDING_AMOUNT = 0
Where PROJECT_ID = P_PROJECT_ID
and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
insert it into table RA_INTERFACE_DISTRIBUTIONS as ORG_ID.
**/
l_org_id := MO_GLOBAL.get_current_org_id;
Insert Single Account rounding amount in Interface Distribution
**/
IF l_rnd_amt <> 0
THEN
INSERT INTO RA_INTERFACE_DISTRIBUTIONS
( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID)
VALUES ('REV', l_rnd_amt ,0,NULL,P_Single_Acct_Ccid, P_Interface_attr1,
P_Interface_attr2, P_Interface_attr3,
P_Interface_attr4, P_Interface_attr5,
P_Interface_attr6, P_Interface_attr7,
P_Interface_attr8, P_Batch_Src,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID, /* Bug # 2244810 */
l_org_id);
INSERT INTO RA_INTERFACE_DISTRIBUTIONS
( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID)
VALUES ('REV',(-1)*( l_rnd_amt ),0,NULL,P_RND_ccid,
P_Interface_attr1,
P_Interface_attr2, P_Interface_attr3,
P_Interface_attr4, P_Interface_attr5,
P_Interface_attr6, P_Interface_attr7,
'RND', P_Batch_Src,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID, /* Bug # 2244810 */
l_org_id);
/** Update Invoice line with line amount,rounding amount
**/
UPDATE PA_DRAFT_INVOICE_ITEMS
SET ACCT_AMOUNT = l_func_line,
ROUNDING_AMOUNT = l_rnd_amt
Where PROJECT_ID = P_PROJECT_ID
and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
compute the possible rounding in accounting entries,and insert proper
distribution entries in RA interface distribution. Then perform the
update of invoice line with the computed line amount,UBR,UER in acco
unting currency.
**/
PROCEDURE Create_RND_Entries ( P_Batch_Src IN VARCHAR2,
P_Interface_attr1 IN VARCHAR2,
P_Interface_attr2 IN VARCHAR2,
P_Interface_attr3 IN VARCHAR2,
P_Interface_attr4 IN VARCHAR2,
P_Interface_attr5 IN VARCHAR2,
P_Interface_attr6 IN VARCHAR2,
P_Interface_attr7 IN VARCHAR2,
P_Func_currency_code IN VARCHAR2,
P_Inv_currency_code IN VARCHAR2,
P_Inv_rate_type IN VARCHAR2,
P_Inv_rate_date IN DATE,
P_Inv_exchange_rate IN NUMBER,
P_UBR_ccid IN NUMBER,
P_UER_ccid IN NUMBER,
P_RND_ccid IN NUMBER,
P_Inv_line_amt IN NUMBER,
P_Prj_ubr_amt IN NUMBER,
P_Prj_uer_amt IN NUMBER,
P_Inv_ubr_amt IN NUMBER,
P_Inv_uer_amt IN NUMBER,
P_Project_id IN NUMBER,
P_Conv_rate IN NUMBER,
P_Draft_inv_num IN NUMBER )
AS
l_rate NUMBER;
/** Update the lines Acct amount same as project currency amount **/
UPDATE PA_DRAFT_INVOICE_ITEMS
/* MCB2 change
SET ACCT_AMOUNT = AMOUNT,
*/
SET ACCT_AMOUNT = PROJFUNC_BILL_AMOUNT,
ROUNDING_AMOUNT = 0,
UNBILLED_ROUNDING_AMOUNT_DR = 0,
UNEARNED_ROUNDING_AMOUNT_CR = 0
Where PROJECT_ID = P_PROJECT_ID
and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
insert it into table RA_INTERFACE_DISTRIBUTIONS as ORG_ID.
**/
l_org_id := MO_GLOBAL.get_current_org_id;
Insert UBR rounding amount in Interface Distribution
**/
/**
Shared services changes: Insert the org ID
**/
IF l_rnd_UBR <> 0
THEN
INSERT INTO RA_INTERFACE_DISTRIBUTIONS
( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID)
VALUES ('REV', l_rnd_UBR,0,NULL,P_UBR_ccid, P_Interface_attr1,
P_Interface_attr2, P_Interface_attr3,
P_Interface_attr4, P_Interface_attr5,
P_Interface_attr6, P_Interface_attr7,
'UBR', P_Batch_Src,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
Insert UER rounding amount in Interface Distribution
**/
/**
Shared services changes: Insert the org ID
**/
IF l_rnd_UER <> 0
THEN
INSERT INTO RA_INTERFACE_DISTRIBUTIONS
( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID)
VALUES ('REV', l_rnd_UER,0,NULL,P_UER_ccid, P_Interface_attr1,
P_Interface_attr2, P_Interface_attr3,
P_Interface_attr4, P_Interface_attr5,
P_Interface_attr6, P_Interface_attr7,
'UER', P_Batch_Src,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
Shared services changes: Insert the org ID
**/
IF (l_rnd_UBR + l_rnd_UER) <> 0
THEN
INSERT INTO RA_INTERFACE_DISTRIBUTIONS
( ACCOUNT_CLASS, ACCTD_AMOUNT,AMOUNT,PERCENT,
CODE_COMBINATION_ID, INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2, INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4, INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6, INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8,INTERFACE_LINE_CONTEXT,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID)
VALUES ('REV',(-1)*( l_rnd_UBR + l_rnd_UER),0,NULL,P_RND_ccid,
P_Interface_attr1,
P_Interface_attr2, P_Interface_attr3,
P_Interface_attr4, P_Interface_attr5,
P_Interface_attr6, P_Interface_attr7,
'RND', P_Batch_Src,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
/** Update Invoice line with line amount,rounding amount,UBR Rounding
,UER rounding amount in accounting currency.
**/
UPDATE PA_DRAFT_INVOICE_ITEMS
SET ACCT_AMOUNT = l_func_line,
/* MCB2 change
ROUNDING_AMOUNT = l_func_line - AMOUNT,
*/
ROUNDING_AMOUNT = l_func_line - PROJFUNC_BILL_AMOUNT,
UNBILLED_ROUNDING_AMOUNT_DR = (-1)*l_rnd_UBR,
UNEARNED_ROUNDING_AMOUNT_CR = l_rnd_UER
Where PROJECT_ID = P_PROJECT_ID
and DRAFT_INVOICE_NUM = P_DRAFT_INV_NUM
and LINE_NUM = TO_NUMBER(RTRIM(LTRIM(P_Interface_attr6)));
insert the entries in Receivable Interface distribution table.
**/
PROCEDURE Ins_Dist_Lines(P_Transfer_Mode IN VARCHAR2,
P_Project_Id IN NUMBER,
P_Project_Num IN VARCHAR2,
P_Inv_Num IN NUMBER,
P_Inv_Curr IN VARCHAR2,
P_Proj_Func_Cur IN VARCHAR2,
P_WO_Ccid IN NUMBER,
P_UBR_Ccid IN NUMBER,
P_UER_Ccid IN NUMBER,
P_REC_Ccid IN NUMBER,
P_RND_Ccid IN NUMBER,
P_UNB_ret_Ccid IN NUMBER,
P_Reason_Code IN VARCHAR2,
P_Batch_Src IN VARCHAR2,
P_Trx_Num IN VARCHAR2,
P_Conv_Rate IN NUMBER,
P_Retn_Acct_Flag IN VARCHAR2)
AS
CURSOR get_single_line
IS
SELECT I.AMOUNT AR_AMOUNT,
I.INTERFACE_LINE_ATTRIBUTE1 attr1,
I.INTERFACE_LINE_ATTRIBUTE2 attr2,
I.INTERFACE_LINE_ATTRIBUTE3 attr3,
I.INTERFACE_LINE_ATTRIBUTE4 attr4,
I.INTERFACE_LINE_ATTRIBUTE5 attr5,
I.INTERFACE_LINE_ATTRIBUTE6 attr6,
I.INTERFACE_LINE_ATTRIBUTE7 attr7,
decode(P_Transfer_Mode,'INTERCOMPANY','ICREV','WO') attr8,
/* MCB2 change
DII.AMOUNT Proj_line_amt,
*/
DII.PROJFUNC_BILL_AMOUNT Proj_line_amt,
Decode(P_Transfer_Mode,'INTERCOMPANY',
DII.CC_REV_CODE_COMBINATION_ID,P_WO_Ccid) rev_ccid
FROM RA_INTERFACE_LINES I,
PA_DRAFT_INVOICE_ITEMS DII
WHERE I.INTERFACE_LINE_ATTRIBUTE1||'' = P_Project_Num
AND rtrim(ltrim(I.INTERFACE_LINE_ATTRIBUTE2)) =
rtrim(ltrim(to_char(P_Inv_Num)))
-- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
AND I.BATCH_SOURCE_NAME = P_Batch_Src
AND I.TRX_NUMBER = P_Trx_Num
AND DII.PROJECT_ID = P_Project_Id
AND DII.DRAFT_INVOICE_NUM = P_Inv_Num
AND DII.LINE_NUM
= to_number(TRUNC(I.INTERFACE_LINE_ATTRIBUTE6));
SELECT I.AMOUNT AR_AMOUNT,
I.INTERFACE_LINE_ATTRIBUTE1 ATTR1,
I.INTERFACE_LINE_ATTRIBUTE2 ATTR2,
I.INTERFACE_LINE_ATTRIBUTE3 ATTR3,
I.INTERFACE_LINE_ATTRIBUTE4 ATTR4,
I.INTERFACE_LINE_ATTRIBUTE5 ATTR5,
I.INTERFACE_LINE_ATTRIBUTE6 ATTR6,
I.INTERFACE_LINE_ATTRIBUTE7 ATTR7,
I.CONVERSION_RATE CRATE,
I.CONVERSION_DATE CDATE,
I.CONVERSION_TYPE CTYPE
FROM RA_INTERFACE_LINES I
WHERE I.INTERFACE_LINE_ATTRIBUTE1||'' = P_Project_Num
AND rtrim(ltrim(I.INTERFACE_LINE_ATTRIBUTE2)) =
rtrim(ltrim(to_char(P_Inv_Num)))
-- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
AND I.BATCH_SOURCE_NAME = P_Batch_Src
AND I.TRX_NUMBER = P_Trx_Num ;
AND I.BATCH_SOURCE_NAME = (SELECT RBS.NAME FROM
RA_BATCH_SOURCES RBS,PA_IMPLEMENTATIONS IMP
WHERE RBS.BATCH_SOURCE_ID
=IMP.INVOICE_BATCH_SOURCE_ID);
SELECT DECODE(P_Proj_Func_Cur,P_Inv_Curr,(-1)*(DII.UNBILLED_RECEIVABLE_DR),
/* MCB2 change
DECODE((-1)*(DII.UNBILLED_RECEIVABLE_DR),DII.AMOUNT,l_ar_amt,
PA_CURRENCY.round_trans_currency_amt((l_ar_amt/DII.AMOUNT)*
*/
DECODE((-1)*(DII.UNBILLED_RECEIVABLE_DR),DII.PROJFUNC_BILL_AMOUNT,l_ar_amt,
PA_CURRENCY.round_trans_currency_amt((l_ar_amt/DII.PROJFUNC_BILL_AMOUNT)*
(-1)*(DII.UNBILLED_RECEIVABLE_DR),P_Inv_Curr))) INV_UBR,
(-1)*(nvl(DII.UNBILLED_RECEIVABLE_DR,0)) PRJ_UBR,
(nvl(DII.UNEARNED_REVENUE_CR,0)) PRJ_UER,
dii.invoice_line_type, /* Retention Enhancement */
dii.projfunc_bill_amount,
dii.inv_amount
FROM PA_DRAFT_INVOICE_ITEMS DII
WHERE DII.PROJECT_ID = P_Project_Id
AND DII.DRAFT_INVOICE_NUM = P_Inv_Num
AND DII.LINE_NUM = to_number(l_line_num);
insert it into table RA_INTERFACE_DISTRIBUTIONS as ORG_ID.
**/
l_org_id := MO_GLOBAL.get_current_org_id;
/* Insert the Write-off/Intercompany accounting */
/* Shared services changes: Insert the org ID */
INSERT INTO RA_Interface_Distributions
(
Account_Class, Amount, Percent, Code_Combination_ID,
Interface_Line_Attribute1, Interface_Line_Attribute2,
Interface_Line_Attribute3, Interface_Line_Attribute4,
Interface_Line_Attribute5, Interface_Line_Attribute6,
Interface_Line_Attribute7, Interface_Line_Attribute8,
Interface_Line_Context,Acctd_Amount,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID
)
values( 'REV',
get_woff_line_rec.AR_AMOUNT,
NULL,
get_woff_line_rec.rev_ccid,
get_woff_line_rec.ATTR1,
get_woff_line_rec.ATTR2,
get_woff_line_rec.ATTR3,
get_woff_line_rec.ATTR4,
get_woff_line_rec.ATTR5,
get_woff_line_rec.ATTR6,
get_woff_line_rec.ATTR7,
get_woff_line_rec.ATTR8,
P_Batch_Src,
decode(l_acct_amt,0,get_woff_line_rec.Proj_Line_amt,l_acct_amt),
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
and invoice line type = 'RETENTION' then insert new line in ra_interface_distribution table
for the unbill retention account. */
IF ((P_Retn_Acct_Flag = 'Y') AND (cur_get_line_info.invoice_line_type = 'RETENTION')) THEN
/* Retention Enhnancement : Creating Rounding Entries for Retention */
Cr_Single_RND_Entries ( P_Batch_Src => P_Batch_Src,
P_Interface_attr1 => cur_get_acct_info.attr1,
P_Interface_attr2 => cur_get_acct_info.attr2,
P_Interface_attr3 => cur_get_acct_info.attr3,
P_Interface_attr4 => cur_get_acct_info.attr4,
P_Interface_attr5 => cur_get_acct_info.attr5,
P_Interface_attr6 => cur_get_acct_info.attr6,
P_Interface_attr7 => cur_get_acct_info.attr7,
P_Interface_attr8 => 'UNB-RET',
P_Func_currency_code => P_Proj_Func_Cur,
P_Inv_currency_code => P_Inv_curr,
P_Single_Acct_Ccid => P_UNB_ret_Ccid,
P_RND_ccid => P_RND_Ccid,
P_Inv_line_amt => l_ar_amount,
P_Proj_line_amt => cur_get_line_info.projfunc_bill_amount,
P_Project_id => P_Project_Id,
P_Conv_rate => P_Conv_rate,
P_Draft_inv_num => P_Inv_Num,
X_Acct_amt => l_acct_amt);
/* Shared services changes: Insert the org ID */
INSERT INTO RA_Interface_Distributions
(
Account_Class, Amount, Percent, Code_Combination_ID,
Interface_Line_Attribute1, Interface_Line_Attribute2,
Interface_Line_Attribute3, Interface_Line_Attribute4,
Interface_Line_Attribute5, Interface_Line_Attribute6,
Interface_Line_Attribute7, Interface_Line_Attribute8,
Interface_Line_Context,Acctd_amount,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID
)
VALUES ('REV',
l_ar_amount,
NULL,
P_UNB_ret_Ccid,
cur_get_acct_info.ATTR1,
cur_get_acct_info.ATTR2,
cur_get_acct_info.ATTR3,
cur_get_acct_info.ATTR4,
cur_get_acct_info.ATTR5,
cur_get_acct_info.ATTR6,
cur_get_acct_info.ATTR7,
'UNB-RET',
P_Batch_Src,
decode(l_acct_amt,0, cur_get_line_info.projfunc_bill_amount, l_acct_amt),
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
/* Insert UBR entry for each lines */
/* Shared services changes: Insert the org ID */
IF (nvl(cur_get_line_info.INV_UBR,0) <> 0 or PA_BILLING.GETINVOICENZ = 'Y') /* Added Additonal condition for BUG 8666892 */
THEN
INSERT INTO RA_Interface_Distributions
(
Account_Class, Amount, Percent, Code_Combination_ID,
Interface_Line_Attribute1, Interface_Line_Attribute2,
Interface_Line_Attribute3, Interface_Line_Attribute4,
Interface_Line_Attribute5, Interface_Line_Attribute6,
Interface_Line_Attribute7, Interface_Line_Attribute8,
Interface_Line_Context,Acctd_amount,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG #2244810 */
ORG_ID
)
VALUES ('REV',
cur_get_line_info.INV_UBR,
NULL,
P_UBR_Ccid,
cur_get_acct_info.ATTR1,
cur_get_acct_info.ATTR2,
cur_get_acct_info.ATTR3,
cur_get_acct_info.ATTR4,
cur_get_acct_info.ATTR5,
cur_get_acct_info.ATTR6,
cur_get_acct_info.ATTR7,
'UBR',
P_Batch_Src,
cur_get_line_info.PRJ_UBR,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
/* Insert UER entry for each lines */
/* Shared services changes: Insert the org ID */
IF (l_inv_uer <> 0 or PA_BILLING.GETINVOICENZ = 'Y') /* Added Additonal condition for BUG 8666892 */
THEN
INSERT INTO RA_Interface_Distributions
(
Account_Class, Amount, Percent, Code_Combination_ID,
Interface_Line_Attribute1, Interface_Line_Attribute2,
Interface_Line_Attribute3, Interface_Line_Attribute4,
Interface_Line_Attribute5, Interface_Line_Attribute6,
Interface_Line_Attribute7, Interface_Line_Attribute8,
Interface_Line_Context,Acctd_Amount,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG #2244810 */
ORG_ID
)
VALUES ('REV',
l_inv_uer,
NULL,
P_UER_Ccid,
cur_get_acct_info.ATTR1,
cur_get_acct_info.ATTR2,
cur_get_acct_info.ATTR3,
cur_get_acct_info.ATTR4,
cur_get_acct_info.ATTR5,
cur_get_acct_info.ATTR6,
cur_get_acct_info.ATTR7,
'UER',
P_Batch_Src,
cur_get_line_info.PRJ_UER,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id);
/*Added the select statement for bug 7665769 */
SELECT min(to_number(rtrim(ltrim(I.Interface_Line_Attribute6))))
INTO l_min_line
FROM ra_interface_lines I
WHERE rtrim(ltrim(I.Interface_Line_Attribute2)) = rtrim(ltrim(to_char(P_Inv_Num)))
AND I.Interface_Line_Attribute1 = P_Project_Num
AND I.BATCH_SOURCE_NAME = P_Batch_Src
AND I.TRX_NUMBER = P_Trx_Num;
/* Insert the Receivable Accounting for the Invoice */
/* Shared services changes: Insert the org ID */
INSERT INTO RA_Interface_Distributions
(
Account_Class, Amount, Percent, Code_Combination_ID,
Interface_Line_Attribute1, Interface_Line_Attribute2,
Interface_Line_Attribute3, Interface_Line_Attribute4,
Interface_Line_Attribute5, Interface_Line_Attribute6,
Interface_Line_Attribute7, Interface_Line_Attribute8,
Interface_Line_Context,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, /* BUG # 2244810 */
ORG_ID
)
SELECT 'REC',
NULL,
100,
P_REC_Ccid,
I.Interface_Line_Attribute1,
I.Interface_Line_Attribute2,
I.Interface_Line_Attribute3,
I.Interface_Line_Attribute4,
I.Interface_Line_Attribute5,
I.Interface_Line_Attribute6,
I.Interface_Line_Attribute7,
NULL,
P_Batch_Src,
FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,
l_org_id
FROM RA_Interface_lines I
WHERE rtrim(ltrim(I.Interface_Line_Attribute2)) =
rtrim(ltrim(to_char(P_Inv_Num)))
AND to_number(rtrim(ltrim(I.Interface_Line_Attribute6))) = l_min_line --Modified the condition for bug 7665769
AND I.Interface_Line_Attribute1 = P_Project_Num
-- AND I.INTERFACE_LINE_CONTEXT = P_Batch_Src -- Performance Bug 2695303
AND I.BATCH_SOURCE_NAME = P_Batch_Src
AND I.TRX_NUMBER = P_Trx_Num;
SELECT ORG_DI.DRAFT_INVOICE_NUM cm_inv_num,
INT_LINE.currency_code invoice_currency_code,
ORG_DI.DRAFT_INVOICE_NUM_CREDITED orig_inv_num,
0 CUST_TRX_ID,
'N' CM_CAN_FLAG,
ORG_DI.RA_INVOICE_NUMBER CM_TRX_NUM,
/* INT_LINE.reason_code REASON_CODE, Changed for
credit memo reason*/
DECODE(ORG_DI.Draft_Invoice_Num_Credited, NULL, '',
DECODE(NVL(ORG_DI.Write_Off_Flag, 'N'), 'N',
'PA_CREDIT_MEMO', 'PA_WRITE_OFF')) REASON_CODE,
/* INT_LINE.Interface_Line_Context SOURCE, Commented for bug 3502647 */
INT_LINE.BATCH_SOURCE_NAME SOURCE, /* Added for bug 3502647 */
ORG_DI.PROJFUNC_INVTRANS_RATE_DATE exchg_date,
ORG_DI.PROJFUNC_INVTRANS_RATE_TYPE exchg_type,
ORG_DI.INV_CURRENCY_CODE inv_curr_code,
INT_LINE.CONVERSION_RATE exchg_rate
FROM ra_interface_lines INT_LINE,
pa_draft_invoices ORG_DI
WHERE INT_LINE.interface_line_attribute1||'' = P_Project_Num
AND ORG_DI.request_id = P_Request_Id
AND ltrim(rtrim(INT_LINE.interface_line_attribute2))
= ltrim(rtrim(to_char(ORG_DI.Draft_invoice_num)))
AND ORG_DI.project_id = P_Project_Id
AND ORG_DI.DRAFT_INVOICE_NUM_CREDITED is NULL
AND INT_LINE.BATCH_SOURCE_NAME = P_Batch_Src
AND INT_LINE.TRX_NUMBER = ORG_DI.RA_INVOICE_NUMBER
UNION
SELECT CM_DI.DRAFT_INVOICE_NUM cm_inv_num,
INT_LINE.currency_code invoice_currency_code,
CM_DI.DRAFT_INVOICE_NUM_CREDITED orig_inv_num,
nvl(ORG_DI.SYSTEM_REFERENCE,0) CUST_TRX_ID,
nvl(ORG_DI.CANCELED_FLAG,'N') CM_CAN_FLAG,
CM_DI.RA_INVOICE_NUMBER CM_TRX_NUM,
/* INT_LINE.reason_code REASON_CODE, changed for credit memo
reason*/
DECODE(CM_DI.Draft_Invoice_Num_Credited, NULL, '',
DECODE(NVL(CM_DI.Write_Off_Flag, 'N'), 'N',
'PA_CREDIT_MEMO', 'PA_WRITE_OFF')) REASON_CODE,
/* INT_LINE.Interface_Line_Context SOURCE, Commented for bug 3502647 */
INT_LINE.BATCH_SOURCE_NAME SOURCE, /* Added for bug 3502647 */
CM_DI.PROJFUNC_INVTRANS_RATE_DATE exchg_date,
CM_DI.PROJFUNC_INVTRANS_RATE_TYPE exchg_type,
CM_DI.INV_CURRENCY_CODE inv_curr_code,
INT_LINE.CONVERSION_RATE exchg_rate
FROM ra_interface_lines INT_LINE,
pa_draft_invoices CM_DI,
pa_draft_invoices ORG_DI,
ra_customer_trx TRX,
ra_batch_sources SOURCE
WHERE INT_LINE.interface_line_attribute1 = P_Project_Num
AND CM_DI.request_id = P_Request_Id
AND ltrim(rtrim(INT_LINE.interface_line_attribute2))
= ltrim(rtrim(to_char(CM_DI.Draft_invoice_num)))
AND CM_DI.project_id = P_Project_Id
AND CM_DI.PROJECT_ID = ORG_DI.PROJECT_ID
AND CM_DI.DRAFT_INVOICE_NUM_CREDITED = ORG_DI.DRAFT_INVOICE_NUM
AND CM_DI.DRAFT_INVOICE_NUM_CREDITED is NOT NULL
AND ORG_DI.SYSTEM_REFERENCE = TRX.CUSTOMER_TRX_ID
AND TRX.BATCH_SOURCE_ID = SOURCE.BATCH_SOURCE_ID
AND INT_LINE.Batch_Source_Name = SOURCE.NAME
AND INT_LINE.TRX_NUMBER = CM_DI.RA_INVOICE_NUMBER
AND INT_LINE.BATCH_SOURCE_NAME = P_Batch_Src; /* 2366742 */
SELECT SUM(DII.AMOUNT) /SUM(DII.INV_AMOUNT)
INTO l_rate
FROM PA_DRAFT_INVOICE_ITEMS DII
WHERE DII.PROJECT_ID = P_Project_Id
AND DII.DRAFT_INVOICE_NUM = cur_get_inv_info.cm_inv_num;
Update PA_DRAFT_INVOICES
set ACCTD_CURR_CODE = P_Proj_Func_Cur,
ACCTD_RATE_TYPE = decode(cur_get_inv_info.inv_curr_code,
P_Proj_Func_Cur,NULL,PA_EXCHG_TYPE),
ACCTD_RATE_DATE = decode(cur_get_inv_info.inv_curr_code,
P_Proj_Func_Cur,NULL,cur_get_inv_info.exchg_date),
ACCTD_EXCHG_RATE = decode(cur_get_inv_info.inv_curr_code,
P_Proj_Func_Cur,NULL,l_rate)
Where project_id = P_Project_Id
And draft_invoice_num= cur_get_inv_info.cm_inv_num;
/* Insert the accounting entry for UBR/UER/WO/IC */
Ins_Dist_lines(P_Transfer_Mode,
P_Project_Id,
P_Project_Num,
cur_get_inv_info.cm_inv_num,
cur_get_inv_info.invoice_currency_code,
P_Proj_Func_Cur,
P_WO_Ccid,
P_UBR_Ccid,
P_UER_Ccid,
P_REC_Ccid,
P_RND_Ccid,
P_UNB_ret_Ccid,
cur_get_inv_info.REASON_CODE,
cur_get_inv_info.SOURCE,
cur_get_inv_info.CM_TRX_NUM,
l_rate,
P_Retn_Acct_Flag);
SELECT 'x'
INTO l_dummy
FROM PA_FUNCTION_TRANSACTIONS
WHERE FUNCTION_CODE = P_Func_code
AND function_transaction_code NOT IN ('RLZD-GAIN', 'RLZD-LOSS')
AND nvl(ENABLED_FLAG,'N') = 'N'
AND rownum = 1;
SELECT 'x'
INTO l_dummy
FROM PA_FUNCTION_TRANSACTIONS
WHERE FUNCTION_CODE = P_Func_code
/* AND function_transaction_code NOT IN ('UNB-RET','RLZD_GAIN', 'RLZD_LOSS') */
AND function_transaction_code NOT IN ('UNB-RET','RLZD-GAIN', 'RLZD-LOSS')
AND nvl(ENABLED_FLAG,'N') = 'N'
AND rownum = 1;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_Rec_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_UBR_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_UER_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_WO_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_RND_ccid;
SELECT 'x'
INTO l_dummy
FROM gl_code_combinations
WHERE code_combination_id = P_UNB_ret_ccid;
select nvl(meaning ,l_rej_code)
from pa_lookups
where lookup_type = 'TRANSFER REJECTION CODE'
and lookup_code = l_rej_code;
SELECT p_carrying_out_org_id from dual
union all has been added to select the start organization into the cursor
along with the other organizations that are selected from the connect By query.
The second part of select has been modified so as to select organization_id_parent inplace of organization_id_child */
CURSOR cur_org IS
SELECT p_carrying_out_org_id from dual
union all
SELECT struct.organization_id_parent organization_id
FROM per_org_structure_elements struct
CONNECT BY PRIOR
struct.organization_id_parent = struct.organization_id_child
/* AND struct.business_group_id = p_business_group_id */
AND struct.org_structure_version_id + 0 =
p_proj_org_struct_version_id
START WITH struct.organization_id_child
= p_carrying_out_org_id+0
/* AND struct.business_group_id = p_business_group_id */
AND struct.org_structure_version_id + 0 = p_proj_org_struct_version_id;
SELECT TO_CHAR (type.cust_trx_type_id) cust_trx_type_id,
TO_CHAR (type.credit_memo_type_id) credit_memo_type
FROM ra_cust_trx_types type,
hr_all_organization_units org,
hr_all_organization_units_tl org_tl
WHERE org_tl.organization_id = org.organization_id
AND org_tl.language = p_basic_language
AND org.organization_id = ip_org_id
/* AND org.business_group_id = p_business_group_id */
AND type.type = 'INV'
AND trim(type.name) = trim(substrb(org_tl.name,1,17)) /*Modified for bug 6021078 */ /*Modified for bug 9213496*/
/*Modified trim(substrb(org_tl.organization_id||org_tl.name,1,17) to trim(substrb(org_tl.name,1,17) for the bug 12607920*/
AND type.attribute1=to_char(org.organization_id)/*Added for Bug 12607920 as FP*/
AND NVL(p_trans_date,SYSDATE) BETWEEN type.start_date AND NVL (type.end_date, NVL(p_trans_date,SYSDATE)) /* Modified for bug 8687883*/
AND EXISTS (
SELECT 'x'
FROM hr_organization_information orginfo
WHERE orginfo.organization_id = ip_org_id
AND orginfo.org_information_context = 'CLASS'
AND orginfo.org_information1 = 'PA_INVOICE_ORG'
AND orginfo.org_information2 = 'Y' );
SELECT description into p_error_message
FROM pa_lookups
WHERE lookup_type='AR TRANSACTION TYPE MISSING' AND
lookup_code ='AR_TRX_TYPE_NOT_FOUND';
SELECT count(*) into int_ctr
FROM PA_Project_Customers
WHERE Project_ID = P_proj_id
AND NVL(Bill_Another_Project_Flag,'N') = 'Y';