[Home] [Help]
466: /* This CURSOR selects all reporting set of books enabled for the primary set of book id
467: in PA */
468:
469: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
470: new table gl_alc_ledger_rships_v and corresponding columns */
471:
472: /* CURSOR rsob is SELECT rep.reporting_set_of_books_id reporting_set_of_books_id ,
473: rep.reporting_currency_code, rep.conversion_type
474: FROM gl_mc_reporting_options rep, pa_implementations imp
479:
480: CURSOR rsob is SELECT rep.ledger_id reporting_set_of_books_id ,
481: rep.currency_code reporting_currency_code,
482: rep.alc_default_conv_rate_type conversion_type
483: FROM gl_alc_ledger_rships_v rep, pa_implementations imp
484: WHERE rep.source_ledger_id = imp.set_of_books_id
485: AND rep.relationship_enabled_flag = 'Y'
486: AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
487: AND rep.application_id = 275;
492: /* This CURSOR selects all reporting set of books enabled for the primary set of book id
493: in AR */
494:
495: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
496: new table gl_alc_ledger_rships_v and corresponding columns */
497:
498:
499: /* CURSOR rsob_ar_mc is SELECT rep.reporting_set_of_books_id reporting_set_of_books_id ,
500: rep.reporting_currency_code, rep.conversion_type
507:
508: CURSOR rsob_ar_mc is SELECT rep.ledger_id reporting_set_of_books_id ,
509: rep.currency_code reporting_currency_code,
510: rep.alc_default_conv_rate_type conversion_type
511: FROM gl_alc_ledger_rships_v rep, pa_implementations imp
512: WHERE rep.source_ledger_id = imp.set_of_books_id
513: AND rep.relationship_enabled_flag = 'Y'
514: AND (rep.org_id = -99 OR rep.org_id = imp.org_id)
515: AND application_id = PA_FUND_REVAL_UTIL.get_ar_application_id;
1731: for the given project in primary and reporting set of book ids */
1732:
1733:
1734: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will obsoleted, replace with
1735: new table gl_alc_ledger_rships_v and corresponding columns */
1736:
1737: /* mrc migration to SLA bug 4571438
1738: CURSOR get_all_spf_lines (l_ProjectId NUMBER) is
1739: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
1776: PC.retention_level_code retention_level_code,
1777: PC.customer_id cust_id
1778: FROM pa_mc_sum_proj_fundings SPF_mc, pa_summary_project_fundings SPF,
1779: pa_agreements_all A, pa_project_customers PC,
1780: gl_alc_ledger_rships_v rep, pa_implementations imp
1781: WHERE SPF.project_id = l_ProjectId
1782: AND A.agreement_id = SPF.agreement_id
1783: AND PC.customer_id = A.customer_id
1784: AND PC.project_id = SPF.project_id
2533: primary and reporint sob
2534: */
2535:
2536: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
2537: new table gl_alc_ledger_rships_v and corresponding columns */
2538: /* mrc migration to SLA bug 4571438
2539: CURSOR get_all_retn_invoices IS
2540: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
2541: di.draft_invoice_num drft_inv_num,
2580: sum(dii_mc.amount) projfunc_bill_amount,
2581: sum(dii.funding_bill_amount) funding_bill_amount,
2582: sum(dii.inv_amount) inv_amount
2583: FROM pa_draft_invoice_items dii, pa_mc_draft_inv_items dii_mc, pa_draft_invoices di,
2584: gl_alc_ledger_rships_v rep, pa_implementations imp
2585: WHERE di.project_id = p_project_id
2586: AND di.agreement_id = p_agreement_id
2587: AND di.transfer_status_code = 'A'
2588: AND dii.project_id = di.project_id
3346: primary and reporting set of book ids */
3347:
3348:
3349: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
3350: new table gl_alc_ledger_rships_v and corresponding columns */
3351:
3352: /* mrc migration to SLA bug 4571438
3353: CURSOR get_all_proj_invoices IS
3354: (SELECT 'REGULAR-PROJ' invoice_type,
3442: 0 projfunc_retn_amount,
3443: 0 funding_retn_amount,
3444: 0 inv_retn_amount
3445: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3446: gl_alc_ledger_rships_v rep, pa_implementations imp
3447: WHERE di.project_id = p_project_id
3448: AND di.agreement_id = p_agreement_id
3449: AND dii.project_id = di.project_id
3450: AND dii.draft_invoice_num = di.draft_invoice_num
3487: 0 projfunc_retn_amount,
3488: 0 funding_retn_amount,
3489: 0 inv_retn_amount
3490: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3491: gl_alc_ledger_rships_v rep, pa_implementations imp
3492: WHERE di.project_id = p_project_id
3493: AND di.agreement_id = p_agreement_id
3494: AND dii.project_id = di.project_id
3495: AND dii.draft_invoice_num = di.draft_invoice_num
3630: /* This CURSOR is same as previous CURSOR except that ti will select for both primary and reporting set of
3631: books isd */
3632:
3633: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
3634: new table gl_alc_ledger_rships_v and corresponding columns */
3635:
3636: /* mrc migration to SLA bug 4571438 CURSOR get_all_task_invoices IS
3637: (SELECT 'REGULAR-TASK' invoice_type,
3638: PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
3738: dii.funding_bill_amount,0)) funding_retn_amount,
3739: sum(decode(dii.invoice_line_type,'RETENTION',
3740: dii.inv_amount,0)) inv_retn_amount
3741: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3742: gl_alc_ledger_rships_v rep, pa_implementations imp
3743: WHERE di.project_id = p_project_id
3744: AND di.agreement_id = p_agreement_id
3745: AND dii.project_id = di.project_id
3746: AND dii.draft_invoice_num = di.draft_invoice_num
3783: 0 projfunc_retn_amount,
3784: 0 funding_retn_amount,
3785: 0 inv_retn_amount
3786: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
3787: gl_alc_ledger_rships_v rep, pa_implementations imp
3788: WHERE di.project_id = p_project_id
3789: AND di.agreement_id = p_agreement_id
3790: AND dii.project_id = di.project_id
3791: AND dii.draft_invoice_num = di.draft_invoice_num
5886:
5887: /* The following CURSOR is same as above but fetches for both primary and reporting set of books */
5888:
5889: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
5890: new table gl_alc_ledger_rships_v and corresponding columns */
5891:
5892: /* mrc migration to SLA bug 4571438
5893: CURSOR get_all_inv_total IS
5894: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
5906: sum(dii_mc.amount) projfunc_bill_amount,
5907: sum(dii.funding_bill_amount) funding_bill_amount,
5908: sum(dii.inv_amount) inv_amount
5909: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii,
5910: gl_alc_ledger_rships_v rep, pa_implementations imp
5911: WHERE dii.project_id = p_project_id
5912: AND dii.draft_invoice_num = p_draft_inv_num
5913: -- AND dii.invoice_line_type <> 'RETENTION'
5914: AND rep.source_ledger_id = imp.set_of_books_id
6143: AND RDL.draft_invoice_num = DII.draft_invoice_num
6144: AND RDL.draft_invoice_item_line_num = DII.line_num;
6145:
6146: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
6147: new table gl_alc_ledger_rships_v and corresponding columns */
6148:
6149: /* mrc migration to SLA bug 4571438 CURSOR rdl_amount_all IS
6150: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
6151: SUM((rdl.retained_amount/rdl.bill_amount) * rdl.projfunc_bill_amount) retained_amt_pfc,
6161: SELECT RDL_MC.set_of_books_id,
6162: SUM((rdl.retained_amount/rdl.bill_amount) * rdl_mc.amount) retained_amt_pfc,
6163: SUM((rdl.retained_amount/rdl.bill_amount) * rdl.funding_bill_amount) retained_amt_fc
6164: FROM pa_cust_rev_dist_lines RDL, pa_draft_invoice_items DII, pa_mc_cust_rdl_all RDL_MC,
6165: gl_alc_ledger_rships_v rep, pa_implementations imp
6166: WHERE DII.project_id = p_project_id
6167: AND DII.draft_invoice_num = p_draft_inv_num
6168: AND NVL(DII.task_id,0) = p_task_id
6169: AND RDL.project_id = DII.project_id
6193: AND ERDL.draft_invoice_item_line_num = DII.line_num;
6194:
6195:
6196: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
6197: new table gl_alc_ledger_rships_v and corresponding columns */
6198:
6199: /* mrc migration to SLA bug 4571438 CURSOR erdl_amount_all IS
6200: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
6201: SUM((erdl.retained_amount/erdl.amount) * erdl.projfunc_bill_amount) retained_amt_pfc,
6212: SUM((erdl.retained_amount/erdl.amount) * ERDL_MC.amount) retained_amt_pfc,
6213: SUM((erdl.retained_amount/erdl.amount) * erdl.funding_bill_amount) retained_amt_fc
6214: FROM pa_cust_event_rdl_all ERDL, pa_draft_invoice_items DII,
6215: pa_mc_cust_event_rdl_all ERDL_MC,
6216: gl_alc_ledger_rships_v rep, pa_implementations imp
6217: WHERE DII.project_id = p_project_id
6218: AND DII.draft_invoice_num = p_draft_inv_num
6219: AND NVL(DII.task_id,0) = p_task_id
6220: AND ERDL.project_id = DII.project_id
6243: AND DII.task_id = p_task_id
6244: AND DII.event_num is not null;
6245:
6246: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
6247: new table gl_alc_ledger_rships_v and corresponding columns */
6248:
6249: /* mrc migration to SLA bug 4571438 CURSOR dii_amount_all IS
6250: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
6251: SUM((DII.retained_amount/DII.amount) * DII.projfunc_bill_amount) retained_amt_pfc,
6259: SELECT DII_MC.set_of_books_id,
6260: SUM((DII.retained_amount/DII.amount) * DII_MC.amount) retained_amt_pfc,
6261: SUM((DII.retained_amount/DII.amount) * DII.funding_bill_amount) retained_amt_fc
6262: FROM pa_draft_invoice_items DII, pa_mc_draft_inv_items DII_MC,
6263: gl_alc_ledger_rships_v rep, pa_implementations imp
6264: WHERE DII.project_id = p_project_id
6265: AND DII.draft_invoice_num = p_draft_inv_num
6266: AND DII.task_id = p_task_id
6267: AND rep.source_ledger_id = imp.set_of_books_id
7044: /* The following CURSOR will select all invoices for given project/agreement for
7045: primary and reporting set of book ids */
7046:
7047: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
7048: new table gl_alc_ledger_rships_v and corresponding columns */
7049:
7050: /* mrc migration to SLA bug 4571438 CURSOR get_all_proj_invoices IS
7051: (SELECT PA_FUND_REVAL_PVT.G_SET_OF_BOOKS_ID set_of_books_id,
7052: 0 task_id,
7069: sum(dii_mc.amount) projfunc_bill_amount,
7070: sum(dii.funding_bill_amount) funding_bill_amount
7071: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii,
7072: pa_draft_invoices di,
7073: gl_alc_ledger_rships_v rep, pa_implementations imp
7074: WHERE di.project_id = p_project_id
7075: AND di.agreement_id = p_agreement_id
7076: AND dii.project_id = di.project_id
7077: AND dii.draft_invoice_num = di.draft_invoice_num
7113: GROUP BY dii.task_id
7114: ORDER BY task_id;
7115:
7116: /* R12 : Ledger Architecture Changes : The table gl_mc_reporting_options will be obsolete, replace with
7117: new table gl_alc_ledger_rships_v and corresponding columns */
7118:
7119: /* This CURSOR is same as previous CURSOR except that ti will select for both primary and reporting set of
7120: books isd */
7121: /* mrc migration to SLA bug 4571438 CURSOR get_all_task_invoices IS
7140: sum(dii.amount) amount,
7141: sum(dii_mc.amount) projfunc_bill_amount,
7142: sum(dii.funding_bill_amount) funding_bill_amount
7143: FROM pa_mc_draft_inv_items dii_mc, pa_draft_invoice_items dii, pa_draft_invoices di,
7144: gl_alc_ledger_rships_v rep, pa_implementations imp
7145: WHERE di.project_id = p_project_id
7146: AND di.agreement_id = p_agreement_id
7147: AND dii.project_id = di.project_id
7148: AND dii.draft_invoice_num = di.draft_invoice_num