[Home] [Help]
4: g_pkg_name constant VARCHAR2(30) := 'IEX_UWQ_POP_SUM_TBL_PVT';
5: g_file_name constant VARCHAR2(12) := 'iexvuwpb.pls';
6: G_LOG_ENABLED varchar2(5);
7: G_MSG_LEVEL NUMBER;
8: G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
9: --Begin Bug 8707923 27-Jul-2009 barathsr
10: G_SYSTEM_LEVEL varchar2(100);
11: G_PARTY_LVL_ENB varchar2(1);
12: G_OU_LVL_ENB varchar2(1);
1021: WHERE customer_site_use_id = trx_summ.site_use_id
1022: AND status IN('DELINQUENT', 'PREDELINQUENT')
1023: AND org_id = trx_summ.org_id
1024: AND(uwq_status = 'COMPLETE'
1025: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
1026: )
1027: complete_delinquencies,
1028: (SELECT 1
1029: FROM dual
1124: WHERE pro.cust_account_id = trx_summ.cust_account_id
1125: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1126: AND pro.state = 'BROKEN_PROMISE'
1127: AND(pro.uwq_status = 'COMPLETE'
1128: AND(TRUNC(pro.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
1129: )
1130: ) complete_promises,
1131:
1132: (SELECT 1 FROM dual WHERE EXISTS
1333: cursor c_billto_deln_dtls
1334: is
1335: select del.CUSTOMER_SITE_USE_ID,
1336: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
1337: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
1338: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
1339: from iex_delinquencies_all del,
1340: iex_dln_uwq_summary dln
1341: WHERE del.customer_site_use_id = dln.site_use_id AND
1371:
1372: cursor c_billto_pro_summ is
1373: select del.CUSTOMER_SITE_USE_ID,
1374: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
1375: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
1376: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
1377: from iex_promise_details pd,
1378: IEX_DELINQUENCIES_ALL DEL,
1379: iex_dln_uwq_summary dln
1641: WHERE customer_site_use_id = trx_summ.site_use_id
1642: AND status IN('DELINQUENT', 'PREDELINQUENT')
1643: AND org_id = trx_summ.org_id
1644: AND(uwq_status = 'COMPLETE'
1645: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
1646: )
1647: complete_delinquencies,
1648: (SELECT 1
1649: FROM dual
1743: WHERE pro.cust_account_id = trx_summ.cust_account_id
1744: and del.customer_site_use_id = TRX_SUMM.site_use_ID
1745: AND pro.state = 'BROKEN_PROMISE'
1746: AND(pro.uwq_status = 'COMPLETE'
1747: AND(TRUNC(pro.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
1748: )
1749: ) complete_promises,
1750:
1751: (SELECT 1 FROM dual WHERE EXISTS
1963: cursor c_billto_deln_dtls_dt
1964: is
1965: select del.CUSTOMER_SITE_USE_ID,
1966: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
1967: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
1968: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
1969: from iex_delinquencies_all del,
1970: iex_dln_uwq_summary dln
1971: WHERE del.customer_site_use_id = dln.site_use_id AND
2006:
2007: cursor c_billto_pro_summ_dt is
2008: select del.CUSTOMER_SITE_USE_ID,
2009: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
2010: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
2011: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
2012: from iex_promise_details pd,
2013: IEX_DELINQUENCIES_ALL DEL,
2014: iex_dln_uwq_summary dln
2304: WHERE cust_account_id = trx_summ.cust_account_id
2305: AND status IN('DELINQUENT', 'PREDELINQUENT')
2306: AND org_id = trx_summ.org_id
2307: AND(uwq_status = 'COMPLETE'
2308: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2309: )
2310: complete_delinquencies,
2311: (SELECT 1
2312: FROM dual
2397: FROM iex_promise_details
2398: WHERE cust_account_id = trx_summ.cust_account_id
2399: AND state = 'BROKEN_PROMISE'
2400: AND(uwq_status = 'COMPLETE'
2401: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2402: )
2403: ) complete_promises,
2404:
2405: (SELECT 1 FROM dual WHERE EXISTS
2587: cursor c_acc_deln_dtls
2588: is
2589: select del.cust_account_ID,dln.org_id,
2590: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
2591: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
2592: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
2593: from iex_delinquencies_all del,
2594: iex_dln_uwq_summary dln
2595: WHERE del.cust_account_id = dln.cust_account_id AND
2624:
2625: cursor c_acc_pro_summ is
2626: select del.CUST_Account_ID,dln.org_id,
2627: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
2628: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
2629: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
2630: from iex_promise_details pd,
2631: IEX_DELINQUENCIES_ALL DEL,
2632: iex_dln_uwq_summary dln
2887: WHERE cust_account_id = trx_summ.cust_account_id
2888: AND status IN('DELINQUENT', 'PREDELINQUENT')
2889: AND org_id = trx_summ.org_id
2890: AND(uwq_status = 'COMPLETE'
2891: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2892: )
2893: complete_delinquencies,
2894: (SELECT 1
2895: FROM dual
2979: FROM iex_promise_details
2980: WHERE cust_account_id = trx_summ.cust_account_id
2981: AND state = 'BROKEN_PROMISE'
2982: AND(uwq_status = 'COMPLETE'
2983: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2984: )
2985: ) complete_promises,
2986:
2987: (SELECT 1 FROM dual WHERE EXISTS
3179: cursor c_acc_deln_dtls_dt
3180: is
3181: select del.cust_account_ID,dln.org_id,
3182: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
3183: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
3184: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
3185: from iex_delinquencies_all del,
3186: iex_dln_uwq_summary dln
3187: WHERE del.cust_account_id = dln.cust_account_id
3220:
3221: cursor c_acc_pro_summ_dt is
3222: select del.CUST_Account_ID,dln.org_id,
3223: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
3224: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
3225: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
3226: from iex_promise_details pd,
3227: IEX_DELINQUENCIES_ALL DEL,
3228: iex_dln_uwq_summary dln
3515: WHERE party_cust_id = party.party_id
3516: AND status IN('DELINQUENT', 'PREDELINQUENT')
3517: AND org_id = trx_summ.org_id
3518: AND(uwq_status = 'COMPLETE'
3519: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
3520: )
3521: complete_delinquencies,
3522: (SELECT 1
3523: FROM dual
3590: WHERE b.party_id = party.party_id
3591: AND pd.cust_account_id = b.cust_account_id
3592: AND pd.state = 'BROKEN_PROMISE'
3593: AND(pd.uwq_status = 'COMPLETE'
3594: AND(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
3595: )
3596: complete_promises,
3597: (SELECT 1
3598: FROM dual
3788: cursor c_cu_deln_dtls
3789: is
3790: select del.party_cust_ID,dln.org_id,
3791: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
3792: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
3793: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
3794: from iex_delinquencies_all del,
3795: iex_dln_uwq_summary dln
3796: WHERE del.party_cust_id = dln.party_id AND
3826:
3827: cursor c_cu_pro_summ is
3828: select del.party_cust_ID,dln.org_id,
3829: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
3830: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
3831: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
3832: from iex_promise_details pd,
3833: IEX_DELINQUENCIES_ALL DEL,
3834: iex_dln_uwq_summary dln
4094: WHERE party_cust_id = party.party_id
4095: AND status IN('DELINQUENT', 'PREDELINQUENT')
4096: AND org_id = trx_summ.org_id
4097: AND(uwq_status = 'COMPLETE'
4098: AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
4099: )
4100: complete_delinquencies,
4101: (SELECT 1
4102: FROM dual
4168: WHERE b.party_id = party.party_id
4169: AND pd.cust_account_id = b.cust_account_id
4170: AND pd.state = 'BROKEN_PROMISE'
4171: AND(pd.uwq_status = 'COMPLETE'
4172: AND(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
4173: )
4174: complete_promises,
4175: (SELECT 1
4176: FROM dual
4373: cursor c_cu_deln_dtls_dt
4374: is
4375: select del.party_cust_ID,dln.org_id,
4376: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
4377: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
4378: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
4379: from iex_delinquencies_all del,
4380: iex_dln_uwq_summary dln
4381: WHERE del.party_cust_id = dln.party_id AND
4417:
4418: cursor c_cu_pro_summ_dt is
4419: select del.party_cust_ID,dln.org_id,
4420: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
4421: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
4422: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
4423: from iex_promise_details pd,
4424: IEX_DELINQUENCIES_ALL DEL,
4425: iex_dln_uwq_summary dln
4976: l_from_date := to_date(substr(FROM_DATE, 1, 10), 'YYYY/MM/DD');
4977: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Input FROM_DATE = ' || l_from_date);
4978: FND_FILE.PUT_LINE(FND_FILE.LOG,'Input FROM_DATE = ' || l_from_date);
4979:
4980: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
4981: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Batch size = ' || l_max_fetches);
4982: FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch size = ' || l_max_fetches);
4983:
4984: l_enable_work_queue := nvl(fnd_profile.value('IEX_ENABLE_UWQ_STATUS'), 'N');
4980: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
4981: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Batch size = ' || l_max_fetches);
4982: FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch size = ' || l_max_fetches);
4983:
4984: l_enable_work_queue := nvl(fnd_profile.value('IEX_ENABLE_UWQ_STATUS'), 'N');
4985: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'l_enable_work_queue = ' || l_enable_work_queue);
4986:
4987: --Commented for Bug 8707923 27-JUl-2009 barathsr
4988: --the variable l_level ceases to exist and is replaced by p_level wherever used
4997: return;
4998: END IF;
4999: -- changed for bug 9498399 PNAVEENK
5000: -- l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
5001: -- l_cash := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate'); -- Changed for bug#8630157 by PNAVEENK
5002: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
5003: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
5004: --Start of comment for Bug 8942646 12-Oct-2009 barathsr
5005: --Moved the from_date not null delete part of the code to delete_from_uwq_summ procedure
4998: END IF;
4999: -- changed for bug 9498399 PNAVEENK
5000: -- l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
5001: -- l_cash := NVL(FND_PROFILE.VALUE('IEX_EXCHANGE_RATE_TYPE'), 'Corporate'); -- Changed for bug#8630157 by PNAVEENK
5002: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
5003: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
5004: --Start of comment for Bug 8942646 12-Oct-2009 barathsr
5005: --Moved the from_date not null delete part of the code to delete_from_uwq_summ procedure
5006: /* IF (l_from_date is not null and G_LEVEL_COUNT=0) then --Added for Bug 8707923 27-Jul-2009 barathsr
9374:
9375: --Bug5701973. End.
9376:
9377: -- Start PNAVEENK for bug#7662453 on 22-12-2008
9378: IF nvl(fnd_profile.value('IEX_SHOW_AGING_IN_UWQ'), 'N') = 'Y' then
9379: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Opened Cursor c_aging_summary cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
9380:
9381: IF p_level = 'CUSTOMER' THEN
9382: populate_aging_info('PARTY',l_from_date,p_org_id);--Added for Bug 8707923 27-Jul-2009 barathsr
9391: end if;
9392: -- End for bug#7662453
9393:
9394: --Begin Bug 8823567 22-Oct-2009 barathsr
9395: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
9396: if p_level='CUSTOMER' then
9397: calculate_net_balance('CUSTOMER',l_from_date,p_org_id);
9398: elsif p_level='ACCOUNT' then
9399: calculate_net_balance('ACCOUNT',l_from_date,p_org_id);
9404: --End Bug 8823567 22-Oct-2009 barathsr
9405:
9406:
9407: -- Start for bug#8261043 on 3-3-2009
9408: IF nvl(fnd_profile.value('IEX_SHOW_CONT_IN_UWQ'), 'N') = 'Y' then
9409: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Opened Cursor c_contract_summary cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
9410:
9411: IF l_level in ('CUSTOMER','ACCOUNT','BILL_TO') then
9412: populate_contracts_info;
9852: cursor c_billto_del is
9853: select del.CUSTOMER_SITE_USE_ID,
9854: count(1) number_of_delinquencies,
9855: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
9856: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
9857: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
9858: from iex_delinquencies_all del,
9859: iex_pop_uwq_summ_gt temp
9860: WHERE del.customer_site_use_id = temp.object_id AND
9864:
9865: cursor c_billto_pro is
9866: select del.CUSTOMER_SITE_USE_ID,
9867: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
9868: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
9869: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
9870: from iex_promise_details pd,
9871: IEX_DELINQUENCIES_ALL DEL,
9872: iex_pop_uwq_summ_gt temp
9883: -- Start Bug 14197188 bibeura 26-Jun-2012
9884: -- SUM(amount_due_remaining) broken_promise_amount,
9885: -- SUM(promise_amount) promise_amount
9886: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
9887: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.amount_due_remaining)) broken_promise_amount,
9888: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
9889: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
9890: -- End Bug 14197188 bibeura 26-Jun-2012
9891: FROM iex_promise_details pd,
9885: -- SUM(promise_amount) promise_amount
9886: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
9887: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.amount_due_remaining)) broken_promise_amount,
9888: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
9889: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
9890: -- End Bug 14197188 bibeura 26-Jun-2012
9891: FROM iex_promise_details pd,
9892: iex_delinquencies_all del,
9893: iex_pop_uwq_summ_gt temp,
9963: select del.customer_site_use_id,
9964: sum(decode(del.status,'DELINQUENT',1,'PREDELINQUENT',1,0)) number_of_delinquencies,
9965: sum(decode(del.status,'DELINQUENT',ps.acctd_amount_due_remaining,'PREDELINQUENT',ps.acctd_amount_due_remaining,0)) past_due_inv_value,
9966: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
9967: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
9968: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
9969: del.org_id org_id
9970: from iex_delinquencies del,
9971: ar_payment_schedules ps
9978: group by del.customer_site_use_id, del.org_id;
9979:
9980: BEGIN
9981:
9982: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
9983: -- Bug 14197188 bibeura 26-Jun-2012
9984: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
9985: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
9986: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cash = ' || l_cash);
9980: BEGIN
9981:
9982: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
9983: -- Bug 14197188 bibeura 26-Jun-2012
9984: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
9985: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
9986: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cash = ' || l_cash);
9987:
9988: if p_mode='DLN' then
11179: cursor c_account_del is
11180: select del.cust_account_id,
11181: count(1) number_of_delinquencies,
11182: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
11183: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
11184: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
11185: from iex_delinquencies_all del,
11186: iex_pop_uwq_summ_gt temp
11187: WHERE del.cust_account_id = temp.object_id AND
11191:
11192: cursor c_account_pro is
11193: select del.cust_account_id,
11194: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
11195: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
11196: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
11197: from iex_promise_details pd,
11198: IEX_DELINQUENCIES_ALL DEL,
11199: iex_pop_uwq_summ_gt temp
11210: -- Start Bug 14197188 bibeura 26-Jun-2012
11211: -- SUM(amount_due_remaining) broken_promise_amount,
11212: -- SUM(promise_amount) promise_amount
11213: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
11214: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.amount_due_remaining)) broken_promise_amount,
11215: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
11216: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
11217: -- End Bug 14197188 bibeura 26-Jun-2012
11218: FROM iex_promise_details pd,
11212: -- SUM(promise_amount) promise_amount
11213: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
11214: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.amount_due_remaining)) broken_promise_amount,
11215: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
11216: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
11217: -- End Bug 14197188 bibeura 26-Jun-2012
11218: FROM iex_promise_details pd,
11219: iex_delinquencies_all del,
11220: iex_pop_uwq_summ_gt temp,
11285: select del.cust_account_id,
11286: sum(decode(del.status,'DELINQUENT',1,'PREDELINQUENT',1,0)) number_of_delinquencies,
11287: sum(decode(del.status,'DELINQUENT',ps.acctd_amount_due_remaining,'PREDELINQUENT',ps.acctd_amount_due_remaining,0)) past_due_inv_value,
11288: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
11289: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
11290: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
11291: del.org_id org_id
11292: from iex_delinquencies del,
11293: ar_payment_schedules ps
11300: group by del.cust_account_id, del.org_id;
11301:
11302:
11303: BEGIN
11304: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
11305:
11306: -- Bug 14197188 bibeura 26-Jun-2012
11307: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
11308: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
11303: BEGIN
11304: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
11305:
11306: -- Bug 14197188 bibeura 26-Jun-2012
11307: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
11308: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
11309: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cash = ' || l_cash);
11310:
11311:
12489: cursor c_customer_del is
12490: select del.party_cust_id,
12491: count(1) number_of_delinquencies,
12492: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
12493: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
12494: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies
12495: from iex_delinquencies_all del,
12496: iex_pop_uwq_summ_gt temp
12497: WHERE del.party_cust_id = temp.object_id AND
12501:
12502: cursor c_customer_pro is
12503: select del.party_cust_id,
12504: max(decode(pd.uwq_status,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_promises,
12505: max(decode(pd.uwq_status,'COMPLETE',(decode(sign(TRUNC(pd.uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_promises,
12506: max(decode(pd.uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(pd.uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_promises
12507: from iex_promise_details pd,
12508: IEX_DELINQUENCIES_ALL DEL,
12509: iex_pop_uwq_summ_gt temp
12520: -- Start Bug 14197188 bibeura 26-Jun-2012
12521: -- SUM(amount_due_remaining) broken_promise_amount,
12522: -- SUM(promise_amount) promise_amount
12523: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
12524: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.amount_due_remaining)) broken_promise_amount,
12525: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
12526: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
12527: -- End Bug 14197188 bibeura 26-Jun-2012
12528: FROM iex_promise_details pd,
12522: -- SUM(promise_amount) promise_amount
12523: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
12524: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.amount_due_remaining)) broken_promise_amount,
12525: SUM(gl_currency_api.convert_amount_sql(pd.currency_code, gl.currency_code, sysdate,
12526: iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual'), pd.promise_amount)) promise_amount
12527: -- End Bug 14197188 bibeura 26-Jun-2012
12528: FROM iex_promise_details pd,
12529: iex_delinquencies_all del,
12530: iex_pop_uwq_summ_gt temp,
12597: select del.party_cust_id,
12598: sum(decode(del.status,'DELINQUENT',1,'PREDELINQUENT',1,0)) number_of_delinquencies,
12599: sum(decode(del.status,'DELINQUENT',ps.acctd_amount_due_remaining,'PREDELINQUENT',ps.acctd_amount_due_remaining,0)) past_due_inv_value,
12600: max(decode(uwq_status,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),1,1)))) pending_delinquencies,
12601: max(decode(uwq_status,'COMPLETE',(decode(sign(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') - TRUNC(sysdate)),1,1)))) complete_delinquencies,
12602: max(decode(uwq_status,NULL,1,'ACTIVE',1,'PENDING',(decode(sign(TRUNC(uwq_active_date) - TRUNC(sysdate)),-1,1,0,1)))) active_delinquencies,
12603: del.org_id org_id
12604: from iex_delinquencies del,
12605: ar_payment_schedules ps
12611: and del1.org_id=del.org_id)
12612: group by del.party_cust_id, del.org_id;
12613:
12614: BEGIN
12615: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
12616:
12617: -- Bug 14197188 bibeura 26-Jun-2012
12618: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
12619: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
12614: BEGIN
12615: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
12616:
12617: -- Bug 14197188 bibeura 26-Jun-2012
12618: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE','select NVL(FND_PROFILE.VALUE(''IEX_EXCHANGE_RATE_TYPE''),''Corporate'') from dual');
12619: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
12620: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_cash = ' || l_cash);
12621: if p_mode='DLN' then
12622: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
13577: from_date,
13578: p_mode,
13579: l_allowed_level);
13580: --Begin Bug 8823567 22-Oct-2009 barathsr
13581: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13582: calculate_net_balance(l_allowed_level,from_date,l_curr_org_id);
13583: end if;
13584: --End Bug 8823567 22-Oct-2009 barathsr
13585:
13590: from_date,
13591: p_mode,
13592: l_allowed_level);
13593: --Begin Bug 8823567 22-Oct-2009 barathsr
13594: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13595: calculate_net_balance(l_allowed_level,from_date,l_curr_org_id);
13596: end if;
13597: --End Bug 8823567 22-Oct-2009 barathsr
13598: ELSIF l_allowed_level = 'BILL_TO' THEN
13602: from_date,
13603: p_mode,
13604: l_allowed_level);
13605: --Begin Bug 8823567 22-Oct-2009 barathsr
13606: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13607: calculate_net_balance(l_allowed_level,from_date,l_curr_org_id);
13608: end if;
13609: --End Bug 8823567 22-Oct-2009 barathsr
13610: ELSE
13633: from_date,
13634: p_mode,
13635: l_allowed_level);
13636: --Begin Bug 8823567 22-Oct-2009 barathsr
13637: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13638: calculate_net_balance(l_allowed_level,from_date,null);
13639: end if;
13640: --End Bug 8823567 22-Oct-2009 barathsr
13641:
13646: from_date,
13647: p_mode,
13648: l_allowed_level);
13649: --Begin Bug 8823567 22-Oct-2009 barathsr
13650: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13651: calculate_net_balance(l_allowed_level,from_date,null);
13652: end if;
13653: --End Bug 8823567 22-Oct-2009 barathsr
13654: ELSIF l_allowed_level = 'BILL_TO' THEN
13658: from_date,
13659: p_mode,
13660: l_allowed_level);
13661: --Begin Bug 8823567 22-Oct-2009 barathsr
13662: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13663: calculate_net_balance(l_allowed_level,from_date,null);
13664: end if;
13665: --End Bug 8823567 22-Oct-2009 barathsr
13666: ELSE
13684: from_date,
13685: p_mode,
13686: G_SYSTEM_LEVEL);
13687: --Begin Bug 8823567 22-Oct-2009 barathsr
13688: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13689: calculate_net_balance(G_SYSTEM_LEVEL,from_date,null);
13690: end if;
13691: --End Bug 8823567 22-Oct-2009 barathsr
13692:
13697: from_date,
13698: p_mode,
13699: G_SYSTEM_LEVEL);
13700: --Begin Bug 8823567 22-Oct-2009 barathsr
13701: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13702: calculate_net_balance(G_SYSTEM_LEVEL,from_date,null);
13703: end if;
13704: --End Bug 8823567 22-Oct-2009 barathsr
13705:
13711: p_mode,
13712: G_SYSTEM_LEVEL);
13713:
13714: --Begin Bug 8823567 22-Oct-2009 barathsr
13715: IF nvl(fnd_profile.value('IEX_SHOW_NET_BAL_IN_UWQ'), 'N') = 'Y' then
13716: calculate_net_balance(G_SYSTEM_LEVEL,from_date,null);
13717: end if;
13718: --End Bug 8823567 22-Oct-2009 barathsr
13719: ELSE
13789:
13790:
13791: Begin
13792:
13793: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
13794: l_default_bucket := fnd_profile.value('IEX_COLLECTIONS_BUCKET_NAME') ;
13795: select aging_bucket_id into l_default_bucket_id from ar_aging_buckets where bucket_name = l_default_bucket;
13796:
13797: -- Loop runs for each operating unit once and each time loops for all IEU_PARAM_PK_VALUE values
13790:
13791: Begin
13792:
13793: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
13794: l_default_bucket := fnd_profile.value('IEX_COLLECTIONS_BUCKET_NAME') ;
13795: select aging_bucket_id into l_default_bucket_id from ar_aging_buckets where bucket_name = l_default_bucket;
13796:
13797: -- Loop runs for each operating unit once and each time loops for all IEU_PARAM_PK_VALUE values
13798: for r_org in c_orgs loop
14087: l_org_id l_count;
14088: l_max_fetches number;
14089: temp number;
14090: Begin
14091: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
14092: for r_org in c_orgs loop
14093: temp := 1;
14094: for r_contract in c_contract_summary(r_org.org_id) loop
14095: select count(*) into l_cases_count(temp) from iex_cases_all_b where party_id=r_contract.party_id and org_id=r_org.org_id;
14265: G_LOG_ENABLED := 'N';
14266: G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
14267:
14268: /* getting msg logging info */
14269: G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
14270: if (G_LOG_ENABLED = 'N') then
14271: G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
14272: else
14273: G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
14269: G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
14270: if (G_LOG_ENABLED = 'N') then
14271: G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
14272: else
14273: G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
14274: end if;
14275:
14276: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
14277: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);