[Home] [Help]
1954: else temp1.financial_item_id end financial_item_id, itemaccrel.account_group_id, itemaccrel.natural_account_id
1955: from AMW_FIN_ITEMS_KEY_ACC ITEMACCREL,
1956:
1957: (select -1 financial_item_id, itemb.financial_item_id child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
1958: FROM AMW_CERTIFICATION_B cert,
1959: AMW_FIN_STMNT_ITEMS_B itemb
1960: WHERE cert.CERTIFICATION_ID = P_CERTIFICATION_ID
1961: and cert.statement_group_id = itemb.statement_group_id
1962: and cert.financial_statement_id = itemb.financial_statement_id
1963: UNION ALL
1964: select itemb.financial_item_id, itemflat.child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
1965: from AMW_FIN_ITEM_FLAT itemflat,
1966: AMW_FIN_STMNT_ITEMS_B itemb,
1967: AMW_CERTIFICATION_B cert
1968: where
1969: cert.CERTIFICATION_ID = P_CERTIFICATION_ID
1970: and cert.statement_group_id = itemb.statement_group_id
1971: and cert.financial_statement_id = itemb.financial_statement_id
2024: else temp1.financial_item_id end financial_item_id, itemaccrel.account_group_id, itemaccrel.natural_account_id
2025: from AMW_FIN_ITEMS_KEY_ACC ITEMACCREL,
2026:
2027: (select -1 financial_item_id, itemb.financial_item_id child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
2028: FROM AMW_CERTIFICATION_B cert,
2029: AMW_FIN_STMNT_ITEMS_B itemb
2030: WHERE cert.CERTIFICATION_ID = P_CERTIFICATION_ID
2031: and cert.statement_group_id = itemb.statement_group_id
2032: and cert.financial_statement_id = itemb.financial_statement_id
2033: UNION ALL
2034: select itemb.financial_item_id, itemflat.child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
2035: from AMW_FIN_ITEM_FLAT itemflat,
2036: AMW_FIN_STMNT_ITEMS_B itemb,
2037: AMW_CERTIFICATION_B cert
2038: where
2039: cert.CERTIFICATION_ID = P_CERTIFICATION_ID
2040: and cert.statement_group_id = itemb.statement_group_id
2041: and cert.financial_statement_id = itemb.financial_statement_id
2097: else temp1.financial_item_id end financial_item_id, itemaccrel.account_group_id, itemaccrel.natural_account_id
2098: from AMW_FIN_ITEMS_KEY_ACC ITEMACCREL,
2099:
2100: (select -1 financial_item_id, itemb.financial_item_id child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
2101: FROM AMW_CERTIFICATION_B cert,
2102: AMW_FIN_STMNT_ITEMS_B itemb
2103: WHERE cert.CERTIFICATION_ID = P_CERTIFICATION_ID
2104: and cert.statement_group_id = itemb.statement_group_id
2105: and cert.financial_statement_id = itemb.financial_statement_id
2106: UNION ALL
2107: select itemb.financial_item_id, itemflat.child_financial_item_id, itemb.statement_group_id, itemb.financial_statement_id
2108: from AMW_FIN_ITEM_FLAT itemflat,
2109: AMW_FIN_STMNT_ITEMS_B itemb,
2110: AMW_CERTIFICATION_B cert
2111: where
2112: cert.CERTIFICATION_ID = P_CERTIFICATION_ID
2113: and cert.statement_group_id = itemb.statement_group_id
2114: and cert.financial_statement_id = itemb.financial_statement_id
2949: last_update_login = fnd_global.conc_login_id,
2950: CERT_OPINION_LOG_ID = l_opinion_log_id,
2951: CERT_OPINION_ID = l_opinion_id
2952: WHERE ORGANIZATION_ID = l_pk1
2953: AND FIN_CERTIFICATION_ID IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
2954: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
2955: if(p_commit <> FND_API.g_false)
2956: then commit;
2957: end if;
2976: CERT_OPINION_LOG_ID = l_opinion_log_id,
2977: CERT_OPINION_ID = l_opinion_id
2978: WHERE ORGANIZATION_ID = l_pk3
2979: AND PROCESS_ID = l_pk1
2980: AND FIN_CERTIFICATION_ID IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
2981: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
2982:
2983: if(p_commit <> FND_API.g_false)
2984: then commit;
3100: last_update_login = fnd_global.conc_login_id,
3101: EVAL_OPINION_LOG_ID = l_opinion_log_id,
3102: EVAL_OPINION_ID = l_opinion_id
3103: WHERE ORGANIZATION_ID = l_pk1
3104: AND FIN_CERTIFICATION_ID IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
3105: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
3106: if(p_commit <> FND_API.g_false) then
3107: commit;
3108: end if;
3127: EVAL_OPINION_LOG_ID = l_opinion_log_id,
3128: EVAL_OPINION_ID = l_opinion_id
3129: WHERE ORGANIZATION_ID = l_pk3
3130: AND PROCESS_ID = l_pk1
3131: AND FIN_CERTIFICATION_ID IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
3132: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
3133:
3134: if(p_commit <> FND_API.g_false)
3135: then commit;
3156: WHERE OBJECT_TYPE = 'PROCESS_FINCERT'
3157: AND risk_id = l_pk1
3158: AND pk2= l_pk3
3159: AND pk3= l_pk4
3160: AND pk1 IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
3161: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
3162: if(p_commit <> FND_API.g_false)
3163: then commit;
3164: end if;
3182: pk5 = l_opinion_log_id
3183: WHERE OBJECT_TYPE = 'RISK_FINCERT'
3184: AND control_id = l_pk1
3185: AND pk2 = l_pk3
3186: AND pk1 IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
3187: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
3188:
3189: if(p_commit <> FND_API.g_false)
3190: then commit;
3206: pk5 = l_opinion_log_id
3207: WHERE OBJECT_TYPE = 'CONTROL_FINCERT'
3208: AND audit_procedure_id = l_pk1
3209: AND pk2 = l_pk3 -- organization_id
3210: AND pk1 IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
3211: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
3212:
3213: if(p_commit <> FND_API.g_false)
3214: then commit;
3598: SELECT distinct aa.pk1 organization_id, aa.pk2 process_id, po.revision_number, po.process_org_rev_id
3599: FROM AMW_ACCT_ASSOCIATIONS aa,
3600: AMW_FIN_ITEMS_KEY_ACC fika,
3601: AMW_PROCESS_ORGANIZATION po,
3602: AMW_CERTIFICATION_B cert
3603: WHERE aa.object_type = 'PROCESS_ORG'
3604: AND aa.pk1 = po.organization_id
3605: AND aa.pk2 = po.process_id
3606: AND aa.approval_date is not null
3629: SELECT distinct aa.pk1 organization_id, aa.pk2 process_id, po.revision_number, po.process_org_rev_id
3630: FROM AMW_ACCT_ASSOCIATIONS aa,
3631: AMW_FIN_ITEMS_KEY_ACC fika,
3632: AMW_PROCESS_ORGANIZATION po,
3633: AMW_CERTIFICATION_B cert
3634: WHERE cert.certification_id = p_certification_id
3635: AND aa.object_type = 'PROCESS_ORG'
3636: AND aa.pk1 = po.organization_id
3637: AND aa.pk2 = po.process_id
3680: SELECT AA.PK1
3681: FROM AMW_ACCT_ASSOCIATIONS AA,
3682: ( SELECT ACC.CHILD_NATURAL_ACCOUNT_ID NATURAL_ACCOUNT_ID
3683: FROM AMW_FIN_KEY_ACCT_FLAT ACC,
3684: AMW_CERTIFICATION_B CERT,
3685: AMW_FIN_ITEMS_KEY_ACC FIKA
3686: WHERE CERT.CERTIFICATION_ID = p_certification_id
3687: AND FIKA.STATEMENT_GROUP_ID = CERT.STATEMENT_GROUP_ID
3688: AND FIKA.FINANCIAL_STATEMENT_ID = CERT.FINANCIAL_STATEMENT_ID
3690: AND FIKA.ACCOUNT_GROUP_ID = ACC.ACCOUNT_GROUP_ID
3691: UNION ALL
3692: SELECT ACC.NATURAL_ACCOUNT_ID
3693: FROM AMW_FIN_KEY_ACCOUNTS_B ACC,
3694: AMW_CERTIFICATION_B CERT,
3695: AMW_FIN_ITEMS_KEY_ACC FIKA
3696: WHERE CERT.CERTIFICATION_ID = p_certification_id
3697: AND FIKA.STATEMENT_GROUP_ID = CERT.STATEMENT_GROUP_ID
3698: AND FIKA.FINANCIAL_STATEMENT_ID = CERT.FINANCIAL_STATEMENT_ID
4763: where object_type = 'PROCESS_FINCERT'
4764: and risk_id = risk_rec.risk_id
4765: and PK2 = risk_rec.PK1
4766: and PK3 = risk_rec.PK2
4767: and pk1 in (select certification_id from amw_certification_b
4768: where certification_status in ('ACTIVE','DRAFT'));
4769: END LOOP;
4770: if(p_commit <> FND_API.g_false)
4771: then commit;
4816: x_msg_data OUT nocopy VARCHAR2
4817: )IS
4818: CURSOR c_cert IS
4819: SELECT cert.certification_id, period.start_date,period.end_date
4820: FROM AMW_CERTIFICATION_B cert,
4821: AMW_GL_PERIODS_V period
4822: WHERE cert.certification_period_name = period.period_name
4823: AND cert.certification_period_set_name = period.period_set_name
4824: AND cert.object_type='FIN_STMT'
5096: pk5 = m_opinion_log_id
5097: WHERE OBJECT_TYPE = 'RISK_FINCERT'
5098: AND control_id = control_rec.control_id
5099: AND pk2 = control_rec.pk1
5100: AND pk1 IN (SELECT CERTIFICATION_ID FROM AMW_CERTIFICATION_B
5101: WHERE CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT'));
5102:
5103: END LOOP;
5104:
5150: x_msg_data OUT nocopy VARCHAR2
5151: ) IS
5152: CURSOR c_cert IS
5153: SELECT cert.certification_id, period.start_date,period.end_date
5154: FROM AMW_CERTIFICATION_B cert,
5155: AMW_GL_PERIODS_V period
5156: WHERE cert.certification_period_name = period.period_name
5157: AND cert.certification_period_set_name = period.period_set_name
5158: AND cert.object_type='FIN_STMT'
5442: and AUDIT_PROCEDURE_ID = ap_rec.audit_procedure_id
5443: AND PK2 = AP_REC.PK1 /**organizationId**/
5444: and pk4 = ap_rec.pk3 /**controlId**/
5445: AND PK1 IN (SELECT CERTIFICATION_ID
5446: FROM AMW_CERTIFICATION_B
5447: WHERE CERTIFICATION_STATUS IN ('ACTIVE','DRAFT'));
5448: END LOOP;
5449: if(p_commit <> FND_API.g_false)
5450: then commit;
5494: ) IS
5495:
5496: CURSOR c_cert IS
5497: SELECT cert.certification_id, period.start_date, period.end_date
5498: FROM AMW_CERTIFICATION_B cert,
5499: AMW_GL_PERIODS_V period
5500: WHERE cert.certification_period_name = period.period_name
5501: AND cert.certification_period_set_name = period.period_set_name
5502: AND cert.object_type='FIN_STMT'
6244: certifcationb.CERTIFICATION_ID ,
6245: certifcationb.FINANCIAL_STATEMENT_ID,
6246: certifcationb.STATEMENT_GROUP_ID
6247: FROM
6248: AMW_CERTIFICATION_B certifcationb
6249: where
6250: certifcationb.OBJECT_TYPE='FIN_STMT'
6251: and certifcationb.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT');
6252:
6813: ) IS
6814: -- select all processes in scope for the certification
6815: CURSOR c_cert IS
6816: SELECT cert.CERTIFICATION_ID, period.start_date
6817: FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
6818: WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
6819: AND cert.certification_period_set_name = period.period_set_name
6820: and cert.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT');
6821:
6820: and cert.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT');
6821:
6822: CURSOR c_start_date IS
6823: SELECT period.start_date
6824: FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
6825: WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
6826: AND cert.certification_period_set_name = period.period_set_name
6827: and cert.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT')
6828: AND cert.certification_id = p_certification_id;
6939: CURSOR new_risks_added IS
6940: SELECT count(1)
6941: FROM AMW_RISK_ASSOCIATIONS
6942: WHERE association_creation_date >= (SELECT period.start_date
6943: FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
6944: WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
6945: AND cert.certification_period_set_name = period.period_set_name
6946: AND cert.certification_id = p_certification_id)
6947: AND object_type = 'PROCESS_FINCERT'
6952: CURSOR new_controls_added IS
6953: SELECT count(1)
6954: FROM AMW_CONTROL_ASSOCIATIONS
6955: WHERE association_creation_date >= (SELECT period.start_date
6956: FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
6957: WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
6958: AND cert.certification_period_set_name = period.period_set_name
6959: AND cert.certification_id = p_certification_id)
6960: and pk1 = p_certification_id
7211: total_number_of_ctrls = 0,
7212: total_number_of_orgs = 0
7213: WHERE fin_certification_id IN
7214: (select certifcationb.CERTIFICATION_ID
7215: FROM AMW_CERTIFICATION_B certifcationb
7216: where certifcationb.OBJECT_TYPE='FIN_STMT'
7217: and certifcationb.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT'));
7218: end if;
7219:
7273: total_number_of_ctrls = 0
7274: WHERE fin_certification_id IN
7275: (select
7276: certifcationb.CERTIFICATION_ID
7277: FROM AMW_CERTIFICATION_B certifcationb
7278: where
7279: certifcationb.OBJECT_TYPE='FIN_STMT'
7280: and certifcationb.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT'));
7281:
7349: proc_certified = 0
7350: WHERE fin_certification_id IN
7351: (select
7352: certifcationb.CERTIFICATION_ID
7353: FROM AMW_CERTIFICATION_B certifcationb
7354: where
7355: certifcationb.OBJECT_TYPE='FIN_STMT'
7356: and certifcationb.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT'));
7357:
7403: ORGS_PENDING_IN_SCOPE = 0,
7404: ORGS_PENDING_CERTIFICATION = 0
7405: WHERE certification_id IN
7406: (select certifcationb.CERTIFICATION_ID
7407: FROM AMW_CERTIFICATION_B certifcationb
7408: where certifcationb.OBJECT_TYPE='FIN_STMT'
7409: and certifcationb.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT'));
7410: END IF;
7411:
7447: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS RISK
7448: CURSOR Get_all_items(l_risk_id number, l_org_id number, l_process_id number)IS
7449: SELECT DISTINCT item.FIN_CERTIFICATION_ID, item.STATEMENT_GROUP_ID, item.FINANCIAL_STATEMENT_ID, item.FINANCIAL_ITEM_ID
7450: FROM AMW_FIN_ITEM_ACC_RISK ITEM,
7451: AMW_CERTIFICATION_B cert
7452: WHERE ITEM.RISK_ID = l_risk_id
7453: AND ITEM.ORGANIZATION_ID = l_org_id
7454: AND ITEM.PROCESS_ID = l_process_id
7455: AND ITEM.FINANCIAL_ITEM_ID IS NOT NULL
7459: --CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS RISK
7460: CURSOR Get_all_accts (l_risk_id number, l_org_id number, l_process_id number)IS
7461: SELECT DISTINCT finitemAcc.fin_certification_id, finitemAcc.account_group_id, finitemAcc.natural_account_id
7462: FROM AMW_FIN_ITEM_ACC_RISK finitemAcc,
7463: AMW_CERTIFICATION_B cert
7464: WHERE finitemAcc.RISK_ID = l_risk_id
7465: AND finitemAcc.ORGANIZATION_ID = l_org_id
7466: AND finitemAcc.PROCESS_ID = l_process_id
7467: AND finitemAcc.natural_account_id is not null
7471: --CURSOR TO GET ALL OF FINANCIAL CERTIFICATION WHICH HAS PROCESSES THAT RELATES TO THIS RISK
7472: CURSOR Get_all_fin_cert(l_org_id number, l_process_id number) IS
7473: SELECT DISTINCT proc.FIN_CERTIFICATION_ID
7474: FROM AMW_FIN_PROCESS_EVAL_SUM proc,
7475: AMW_CERTIFICATION_B cert
7476: WHERE proc.organization_id = l_org_id
7477: AND proc.PROCESS_ID = l_process_id
7478: AND proc.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
7479: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
7999: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS CONTROL
8000: CURSOR Get_all_items(l_control_id number, l_org_id number)IS
8001: SELECT DISTINCT item.FIN_CERTIFICATION_ID, item.STATEMENT_GROUP_ID, item.FINANCIAL_STATEMENT_ID, item.FINANCIAL_ITEM_ID
8002: FROM AMW_FIN_ITEM_ACC_CTRL ITEM,
8003: AMW_CERTIFICATION_B cert
8004: WHERE ITEM.CONTROL_ID = l_control_id
8005: AND ITEM.ORGANIZATION_ID = l_org_id
8006: AND ITEM.FINANCIAL_ITEM_ID IS NOT NULL
8007: AND item.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
8010: --CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS RISK
8011: CURSOR Get_all_accts (l_control_id number, l_org_id number)IS
8012: SELECT DISTINCT finitemAcc.fin_certification_id, finitemAcc.account_group_id, finitemAcc.natural_account_id
8013: FROM AMW_FIN_ITEM_ACC_CTRL finitemAcc,
8014: AMW_CERTIFICATION_B cert
8015: WHERE finitemAcc.CONTROL_ID = l_control_id
8016: AND finitemAcc.ORGANIZATION_ID = l_org_id
8017: AND finitemAcc.natural_account_id is not null
8018: AND finitemAcc.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
8021: --CURSOR TO GET ALL OF FINANCIAL CERTIFICATION WHICH HAS PROCESSES THAT RELATES TO THIS CONTROL
8022: CURSOR Get_all_fin_cert(l_org_id number) IS
8023: SELECT DISTINCT proc.FIN_CERTIFICATION_ID
8024: FROM AMW_FIN_PROCESS_EVAL_SUM proc,
8025: AMW_CERTIFICATION_B cert
8026: WHERE proc.organization_id = l_org_id
8027: AND proc.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
8028: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
8029:
8624: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS PROCESS
8625: CURSOR Get_all_items( l_org_id number, l_process_id number)IS
8626: SELECT DISTINCT fin.FIN_CERTIFICATION_ID, fin.STATEMENT_GROUP_ID, fin.FINANCIAL_STATEMENT_ID, fin.FINANCIAL_ITEM_ID
8627: FROM AMW_FIN_CERT_SCOPE fin,
8628: AMW_CERTIFICATION_B cert
8629: WHERE fin.ORGANIZATION_ID = l_org_id
8630: AND fin.PROCESS_ID = l_process_id
8631: AND fin.FINANCIAL_ITEM_ID IS NOT NULL
8632: AND fin.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
8635: ---CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS PROCESS
8636: CURSOR Get_all_accts (l_org_id number, l_process_id number)IS
8637: SELECT DISTINCT fin.fin_certification_id, fin.account_group_id, fin.natural_account_id
8638: FROM AMW_FIN_CERT_SCOPE fin,
8639: AMW_CERTIFICATION_B cert
8640: WHERE fin.ORGANIZATION_ID = l_org_id
8641: AND fin.PROCESS_ID = l_process_id
8642: AND fin.natural_account_id is not null
8643: AND fin.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
8647: --CURSOR TO GET ALL OF FINANCIAL CERTIFICATION WHICH CONTAINS THIS PROCESSE
8648: CURSOR Get_all_fin_cert(l_org_id number, l_process_id number) IS
8649: SELECT proc.FIN_CERTIFICATION_ID
8650: FROM AMW_FIN_PROCESS_EVAL_SUM proc,
8651: AMW_CERTIFICATION_B cert
8652: WHERE proc.organization_id = l_org_id
8653: AND proc.PROCESS_ID = l_process_id
8654: AND proc.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
8655: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
8658: --CURSOR TO GET ALL OF FINCERCIAL CERTIFICATION WHICH THIS PROCESS AS SUB-PROCESS
8659: CURSOR Get_all_fin_process(l_org_id number, l_process_id number) IS
8660: SELECT proc.FIN_CERTIFICATION_ID, proc.PARENT_PROCESS_ID
8661: FROM AMW_FIN_PROCESS_FLAT proc,
8662: AMW_CERTIFICATION_B cert
8663: WHERE proc.ORGANIZATION_ID = l_org_id
8664: AND proc.CHILD_PROCESS_ID = l_process_id
8665: AND cert.CERTIFICATION_ID = proc.FIN_CERTIFICATION_ID
8666: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
9623: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS ORGANIZATION
9624: CURSOR Get_all_items( l_org_id number)IS
9625: SELECT DISTINCT fin.FIN_CERTIFICATION_ID, fin.STATEMENT_GROUP_ID, fin.FINANCIAL_STATEMENT_ID, fin.FINANCIAL_ITEM_ID
9626: FROM AMW_FIN_CERT_SCOPE fin,
9627: AMW_CERTIFICATION_B cert
9628: WHERE fin.ORGANIZATION_ID = l_org_id
9629: AND fin.FINANCIAL_ITEM_ID IS NOT NULL
9630: AND fin.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
9631: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
9633: ---CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS ORGANIZATION
9634: CURSOR Get_all_accts (l_org_id number)IS
9635: SELECT DISTINCT fin.fin_certification_id, fin.account_group_id, fin.natural_account_id
9636: FROM AMW_FIN_CERT_SCOPE fin,
9637: AMW_CERTIFICATION_B cert
9638: WHERE fin.ORGANIZATION_ID = l_org_id
9639: AND fin.natural_account_id is not null
9640: AND fin.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
9641: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
9644: --CURSOR TO GET ALL OF FINANCIAL CERTIFICATION WHICH RELATE THIS ORGANIZATION
9645: CURSOR Get_all_fin_cert(l_org_id number) IS
9646: SELECT org.FIN_CERTIFICATION_ID
9647: FROM AMW_FIN_ORG_EVAL_SUM org,
9648: AMW_CERTIFICATION_B cert
9649: WHERE org.organization_id = l_org_id
9650: AND org.FIN_CERTIFICATION_ID = cert.CERTIFICATION_ID
9651: AND cert.CERTIFICATION_STATUS IN ('ACTIVE', 'DRAFT');
9652: