>- see footer for list info -<
Hi Guys, I have this program (please see attached) to update table manually. But I face problem with trigger. So I have to re code this program to PL/SQL code, I mean all in sql statemtn with single file to execute all. Problem if I dont know how to start and how to declare in SQL. How to do loop and carry data into query in loop. Can anyone help me on this... Please give me an example how to do it in pl/sql.
Please help
Regards,
Shaffiq
<cfoutput>
<!--- to query all project id that have archived status in history table --->
<cfquery name="qry_proj_distinct" datasource="#application.ds#">
SELECT DISTINCT prj_master_id
FROM project_version_status_history
WHERE status_lookup_id = 6
ORDER BY prj_master_id
</cfquery>
<cfif qry_proj_distinct.RecordCount neq 0>
Archived Record #qry_proj_distinct.RecordCount#
<br>
<cfloop query="qry_proj_distinct">
<cfset project_id = qry_proj_distinct.prj_master_id>
<!--- to query project id that have more than 1 record
in project version --->
<cfquery name="qry_chck_projid"
datasource="#application.ds#">
SELECT prj_master_id, COUNT(*) AS Proj_Count
FROM project_version
WHERE prj_master_id = <cfqueryparam
value="#project_id#">
GROUP BY prj_master_id
ORDER BY prj_master_id
</cfquery>
<cfif qry_chck_projid.RecordCount neq 0>
<!--- if got more than 1 record, this project
id should be maintain --->
<cfif qry_chck_projid.Proj_Count gt 1>
#qry_chck_projid.prj_master_id# -
#qry_chck_projid.Proj_Count#
<cfset project_id =
trim(#qry_chck_projid.prj_master_id#)>
<br>
<!--- testing --->
<!--- to query version id that got
archived in history --->
<cfquery name="qry_chck_vers_history"
datasource="#application.ds#">
SELECT prj_version_id
FROM
project_version_status_history
WHERE prj_master_id =
<cfqueryparam value="#project_id#">
AND status_lookup_id = 6
GROUP BY prj_version_id
ORDER BY prj_version_id
</cfquery>
<cfif qry_chck_vers_history.RecordCount
neq 0>
<cfloop
query="qry_chck_vers_history">
<cfset prj_vers_id =
trim(#qry_chck_vers_history.prj_version_id#)>
versid :
#prj_vers_id#<br />
<!--- create new
project id for duplicate archived project --->
<cfquery
name="seq_projid" datasource="#APPLICATION.DS#">
select
PROJECT_MASTER_SEQ.nextval project_master_id from dual
</cfquery>
<cfset n_proj_master_id
= seq_projid.project_master_id>
new proj id :
#n_proj_master_id# <br />
<!--- Select
record to duplicate for archived project --->
<cfquery
name="qry_item_proj_master" datasource="#application.ds#">
SELECT
BUSINESS_PRODUCT_LINE_ID, SITE_ID, PROJECT_DEFINITION_CODE, RESERVE_LOCATION,
DIVESTITURE_DATE,
ESTIMATED_PROJECT_CLOSURE_DATE, US_EPA_ACCOUNT_ID, STATE_ACCOUNT_ID,
ADJOINING_PROPERTY_OWNERS,
BUDGET_CATEGORY_LOOKUP_ID, PROJECT_RANK, PERMIT_NUMBER,
INSURANCE_COST_REC_PERCENT,
GOVERNMENT_COST_REC_PERCENT, TRW_OWN, FACILITY_TYPE, KEY_REGULATORS,
CONSULTANTS, US_EPA_REGION,
SUPER_FUND_IND, LAST_UPDATE_DATE, CREATED_DATE, CREATED_BY, MODIFIED_DATE,
MODIFIED_BY, ACTIVE_IND,
PROJECT_SUMMARY_HISTORY, CURRENT_FACILITY_STATUS, KEY_CONTAMINANTS,
REGULATORY_ISSUES_DRIVERS,
PROJECT_STATUS_DESC, SITE_EXIT_STRATEGY, LEGAL_PROPERTY_DESCRIPTION,
COMMENT_TEXT, PROJECT_NAME,
MAJOR_VERSION, MINOR_VERSION, STATE_SUPER_FUND_IND, DOCUMENT_REC_INFO, NGC,
CURRENCY_ID
FROM
PROJECT_MASTER
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
</cfquery>
<!--- <cfif
len(trim(#qry_item_proj_master.DIVESTITURE_DATE#)) neq 0> --->
<cfset
div_dt = DateFormat(#qry_item_proj_master.DIVESTITURE_DATE#,"DD-MMM-YYYY")>
<cfset
cls_dt = DateFormat(#qry_item_proj_master.ESTIMATED_PROJECT_CLOSURE_DATE#,
"DD-MMM-YYYY")>
<cfset
last_dt = DateFormat(#qry_item_proj_master.LAST_UPDATE_DATE#, "DD-MMM-YYYY")>
<cfset
create_dt = DateFormat(#qry_item_proj_master.CREATED_DATE#, "DD-MMM-YYYY")>
<cfset
modify_dt = DateFormat(#qry_item_proj_master.MODIFIED_DATE#, "DD-MMM-YYYY")>
<!--- insert
into project master with new project id --->
<!--- No
Trigger --->
<cfquery
name="qry_insert_proj_master" datasource="#application.ds#">
INSERT
INTO PROJECT_MASTER
(PRJ_MASTER_ID,
BUSINESS_PRODUCT_LINE_ID,
SITE_ID,
RESERVE_LOCATION,
DIVESTITURE_DATE,
ESTIMATED_PROJECT_CLOSURE_DATE,
US_EPA_ACCOUNT_ID,
STATE_ACCOUNT_ID,
ADJOINING_PROPERTY_OWNERS,
BUDGET_CATEGORY_LOOKUP_ID,
PROJECT_RANK,
PERMIT_NUMBER,
INSURANCE_COST_REC_PERCENT,
GOVERNMENT_COST_REC_PERCENT,
TRW_OWN,
FACILITY_TYPE,
KEY_REGULATORS,
CONSULTANTS,
US_EPA_REGION,
SUPER_FUND_IND,
LAST_UPDATE_DATE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND,
PROJECT_SUMMARY_HISTORY,
CURRENT_FACILITY_STATUS,
KEY_CONTAMINANTS,
REGULATORY_ISSUES_DRIVERS,
PROJECT_STATUS_DESC,
SITE_EXIT_STRATEGY,
LEGAL_PROPERTY_DESCRIPTION,
COMMENT_TEXT,
PROJECT_NAME,
MAJOR_VERSION,
MINOR_VERSION,
STATE_SUPER_FUND_IND,
DOCUMENT_REC_INFO,
NGC,
CURRENCY_ID)
VALUES
(#n_proj_master_id#,
#qry_item_proj_master.BUSINESS_PRODUCT_LINE_ID#,
#qry_item_proj_master.SITE_ID#,
'#qry_item_proj_master.RESERVE_LOCATION#',
'#div_dt#',
'#cls_dt#',
'#qry_item_proj_master.US_EPA_ACCOUNT_ID#',
'#qry_item_proj_master.STATE_ACCOUNT_ID#',
'#qry_item_proj_master.ADJOINING_PROPERTY_OWNERS#',
#qry_item_proj_master.BUDGET_CATEGORY_LOOKUP_ID#,
'#qry_item_proj_master.PROJECT_RANK#',
'#qry_item_proj_master.PERMIT_NUMBER#',
#qry_item_proj_master.INSURANCE_COST_REC_PERCENT#,
#qry_item_proj_master.GOVERNMENT_COST_REC_PERCENT#,
'#qry_item_proj_master.TRW_OWN#',
#qry_item_proj_master.FACILITY_TYPE#,
'#qry_item_proj_master.KEY_REGULATORS#',
'#qry_item_proj_master.CONSULTANTS#',
'#qry_item_proj_master.US_EPA_REGION#',
'#qry_item_proj_master.SUPER_FUND_IND#',
'#last_dt#',
'#create_dt#',
#qry_item_proj_master.CREATED_BY#,
'#modify_dt#',
#qry_item_proj_master.MODIFIED_BY#,
'#qry_item_proj_master.ACTIVE_IND#',
'#qry_item_proj_master.PROJECT_SUMMARY_HISTORY#',
'#qry_item_proj_master.CURRENT_FACILITY_STATUS#',
'#qry_item_proj_master.KEY_CONTAMINANTS#',
'#qry_item_proj_master.REGULATORY_ISSUES_DRIVERS#',
'#qry_item_proj_master.PROJECT_STATUS_DESC#',
'#qry_item_proj_master.SITE_EXIT_STRATEGY#',
'#qry_item_proj_master.LEGAL_PROPERTY_DESCRIPTION#',
'#qry_item_proj_master.COMMENT_TEXT#',
'#qry_item_proj_master.PROJECT_NAME#',
#qry_item_proj_master.MAJOR_VERSION#,
#qry_item_proj_master.MINOR_VERSION#,
'#qry_item_proj_master.STATE_SUPER_FUND_IND#',
'#qry_item_proj_master.DOCUMENT_REC_INFO#',
'#qry_item_proj_master.NGC#',
#qry_item_proj_master.CURRENCY_ID#)
</cfquery>
<b>master
insert</b><br />
<!--- Update
project version table with new project id --->
<!--- No
Trigger --->
<cfquery
name="qry_update_prj_vers" datasource="#application.ds#">
UPDATE
project_version
SET
prj_master_id = #n_proj_master_id#
WHERE
prj_master_id = <cfqueryparam value="#project_id#">
AND
prj_version_id = <cfqueryparam value="#prj_vers_id#">
</cfquery>
<b>version
update</b><br />
<!--- Update
project vers history with new project id --->
<!--- Trigger
PROJ_VER_STATUS_HISTORY_TRIG --->
<cfquery
name="qry_update_vers_history" datasource="#application.ds#">
UPDATE
project_version_status_history
SET
prj_master_id = #n_proj_master_id#
WHERE
prj_master_id = <cfqueryparam value="#project_id#">
AND
prj_version_id = <cfqueryparam value="#prj_vers_id#">
</cfquery>
<b>history
update</b><br />
<!--- Update
opitons table --->
<!--- Trigger
OPTIONS_TRIG --->
<cfquery
name="qry_update_options" datasource="#application.ds#">
UPDATE
options
SET
prj_master_id = #n_proj_master_id#
WHERE
prj_master_id = <cfqueryparam value="#project_id#">
AND
prj_version_id = <cfqueryparam value="#prj_vers_id#">
</cfquery>
<b>options
update</b><br />
<!--- Update
WBS budget amt with new project id --->
<!--- Trigger
WBS_BUDGET_AMT_TRIG --->
<cfquery
name="qry_update_wbs_amt" datasource="#application.ds#">
UPDATE
wbs_budget_amt
SET
prj_master_id = #n_proj_master_id#
WHERE
prj_master_id = <cfqueryparam value="#project_id#">
AND
prj_version_id = <cfqueryparam value="#prj_vers_id#">
</cfquery>
<b>WBS Budget
update</b><br />
<!--- Update
WBS LEVEL AMT COMMENT with new project id--->
<!--- Trigger
WBS_LEVEL_AMT_COMMENT_TRIG & PROJECT_MASTER_TRIG --->
<cfquery
name="qry_update_wbs_lvl" datasource="#application.ds#">
UPDATE
wbs_level_amt_comment
SET
prj_master_id = #n_proj_master_id#
WHERE
prj_master_id = <cfqueryparam value="#project_id#">
AND
prj_version_id = <cfqueryparam value="#prj_vers_id#">
</cfquery>
<b>WBS LEVEL
AMT COMMENT update</b><br />
<!--- Select
risk level data from old project master id --->
<cfquery
name="qry_rsk_lvl_mstr" datasource="#application.ds#">
SELECT
RISKLEVEL_ID,
PRJ_MASTER_ID,
RISKLEVEL_COMMENTS,
HYDRO_FACTOR,
REGULATORY_REL,
SITEOWNERSHIP_MULTIPLIER,
SITESETTING_MULTIPLIER,
FRAMEWORK_MULTIPLIER,
RISKLEVEL_SCORE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND
FROM
RISKLEVEL_MASTER
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
ORDER
BY RISKLEVEL_ID
</cfquery>
<cfif
qry_rsk_lvl_mstr.RecordCount neq 0>
<cfloop
query="qry_rsk_lvl_mstr">
<!--- create risk level id --->
<cfquery name="seq_rsk_lvl_mstr" datasource="#APPLICATION.DS#">
select RISKLEVEL_seq.nextval risk_master_id from dual
</cfquery>
<cfset n_risk_master_id = seq_rsk_lvl_mstr.risk_master_id>
Risk id #n_risk_master_id# <br />
<cfset create_dt = DateFormat(#qry_rsk_lvl_mstr.CREATED_DATE#, "DD-MMM-YYYY")>
<cfset modify_dt = DateFormat(#qry_rsk_lvl_mstr.MODIFIED_DATE#, "DD-MMM-YYYY")>
<!--- insert into risk level master with new project master id --->
<!--- Trigger RISKLEVEL_MASTER_TRIG --->
<cfquery name="qry_insert_risk_lvl_mstr" datasource="#application.ds#">
INSERT INTO RISKLEVEL_MASTER
(RISKLEVEL_ID,
PRJ_MASTER_ID,
RISKLEVEL_COMMENTS,
HYDRO_FACTOR,
REGULATORY_REL,
SITEOWNERSHIP_MULTIPLIER,
SITESETTING_MULTIPLIER,
FRAMEWORK_MULTIPLIER,
RISKLEVEL_SCORE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND)
VALUES
(#n_risk_master_id#, #n_proj_master_id#,
'#qry_insert_risk_lvl_mstr.RISKLEVEL_COMMENTS#',
#qry_insert_risk_lvl_mstr.HYDRO_FACTOR#,
#qry_insert_risk_lvl_mstr.REGULATORY_REL#,
#qry_insert_risk_lvl_mstr.SITEOWNERSHIP_MULTIPLIER#,
#qry_insert_risk_lvl_mstr.SITESETTING_MULTIPLIER#,
#qry_insert_risk_lvl_mstr.FRAMEWORK_MULTIPLIER#,
#qry_insert_risk_lvl_mstr.RISKLEVEL_SCORE#,
'#create_dt#',
#qry_insert_risk_lvl_mstr.CREATED_BY#,
'#modify_dt#',
#qry_insert_risk_lvl_mstr.MODIFIED_BY#,
'#qry_insert_risk_lvl_mstr.ACTIVE_IND#'
</cfquery>
</cfloop>
</cfif>
<b>Risk Level
Created</b><br />
<!--- End of
Risk Level --->
<!--- Check for
project objectives --->
<cfquery
name="qry_chk_proj_obj" datasource="#application.ds#">
SELECT
PROJECT_OBJECTIVE_ID, PRJ_MASTER_ID, PERSON_RESPONSIBLE,
PROJECT_OBJECTIVE_DATE, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, ACTIVE_IND,
PROJECT_OBJECTIVE_ACTION, OBJECTIVE_GOAL
FROM
PROJECT_OBJECTIVES
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
ORDER
BY PROJECT_OBJECTIVE_ID
</cfquery>
<cfif
qry_chk_proj_obj.RecordCount neq 0>
<cfloop
query="qry_chk_proj_obj">
<!--- create objective id --->
<cfquery name="seq_obj" datasource="#APPLICATION.DS#">
select PROJECT_OBJECTIVES_SEQ.nextval object_id from dual
</cfquery>
<cfset n_obj_id = seq_obj.object_id>
project obj id #n_obj_id# <br />
<cfset obj_dt =
DateFormat(#qry_chk_proj_obj.PROJECT_OBJECTIVE_DATE#,"DD-MMM-YYYY")>
<cfset create_dt = DateFormat(#qry_chk_proj_obj.CREATED_DATE#, "DD-MMM-YYYY")>
<cfset modify_dt = DateFormat(#qry_chk_proj_obj.MODIFIED_DATE#, "DD-MMM-YYYY")>
<!--- insert into objective for new project id & obj id --->
<!--- Trigger PROJECT_OBJECTIVES_TRIG --->
<cfquery name="qry_insert_obj" datasource="#application.ds#">
INSERT INTO PROJECT_OBJECTIVES
(PROJECT_OBJECTIVE_ID,
PRJ_MASTER_ID,
PERSON_RESPONSIBLE,
PROJECT_OBJECTIVE_DATE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND,
PROJECT_OBJECTIVE_ACTION,
OBJECTIVE_GOAL)
VALUES
(#n_obj_id#,
#n_proj_master_id#,
'#qry_insert_obj.PERSON_RESPONSIBLE#',
'#obj_dt#',
'#create_dt#',
#qry_insert_obj.CREATED_BY#,
'#modify_dt#',
#qry_insert_obj.MODIFIED_BY#,
'#qry_insert_obj.ACTIVE_IND#',
'#qry_insert_obj.PROJECT_OBJECTIVE_ACTION#',
'#qry_insert_obj.OBJECTIVE_GOAL#')
</cfquery>
</cfloop>
</cfif>
<b>objectives
Created</b><br />
<!--- End of
objectives --->
<!--- check for
project personnel assignment --->
<cfquery
name="qry_chk_proj_psnl_asgmt" datasource="#application.ds#">
SELECT
PRJ_MASTER_ID, PERSONNEL_ROLE_ID, ASSIGNMENT_START_DATE,
ASSIGNMENT_END_DATE, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, ACTIVE_IND
FROM
PROJECT_PERSONNEL_ASSIGNMENT
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
ORDER
BY PERSONNEL_ROLE_ID
</cfquery>
<cfif
qry_chk_proj_psnl_asgmt.RecordCount neq 0>
<cfloop
query="qry_chk_proj_psnl_asgmt">
<!--- <cfquery name="seq_per_role" datasource="#APPLICATION.DS#">
select PERSONNEL_ROLE_ASSIGNMENT_SEQ.nextval pers_role_asgmt from dual
</cfquery>
<cfset n_pers_role_asgmt = seq_per_role.pers_role_asgmt> --->
<cfset start_dt =
DateFormat(#qry_chk_proj_psnl_asgmt.ASSIGNMENT_START_DATE#,"DD-MMM-YYYY")>
<cfset end_dt =
DateFormat(#qry_chk_proj_psnl_asgmt.ASSIGNMENT_END_DATE#,"DD-MMM-YYYY")>
<cfset create_dt = DateFormat(#qry_chk_proj_psnl_asgmt.CREATED_DATE#,
"DD-MMM-YYYY")>
<cfset modify_dt = DateFormat(#qry_chk_proj_psnl_asgmt.MODIFIED_DATE#,
"DD-MMM-YYYY")>
<!--- insert new id to project personnel assignment --->
<!--- Trigger PRJ_PERSONNEL_ASSIGNMENT_TRIG --->
<cfquery name="qry_insert_pro_pers_asgmt" datasource="#application.ds#">
INSERT INTO PROJECT_PERSONNEL_ASSIGNMENT
(PRJ_MASTER_ID,
PERSONNEL_ROLE_ID,
ASSIGNMENT_START_DATE,
ASSIGNMENT_END_DATE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND)
VALUES
(#n_proj_master_id#,
#qry_insert_pro_pers_asgmt.PERSONNEL_ROLE_ID#,
'#start_dt#',
'#end_dt#',
'#create_dt#',
#qry_insert_pro_pers_asgmt.CREATED_BY#,
'#modify_dt#',
#qry_insert_pro_pers_asgmt.MODIFIED_BY#,
'#qry_insert_pro_pers_asgmt.ACTIVE_IND#')
</cfquery>
</cfloop>
</cfif>
<b>proj
personnel assgmt Created</b><br />
<!--- End of
proj personnel assgmt --->
<!--- check for
project ouc assignment--->
<cfquery
name="qry_proj_ouc" datasource="#aplication.ds#">
SELECT
PRJ_MASTER_ID, OUC_ID, SITE_OUC_BEGIN_DATE,
SITE_OUC_END_DATE, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, ACTIVE_IND,
COMMENT_TEXT
FROM
PROJECT_OUC_ASSIGNMENT
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
ORDER
BY OUC_ID
</cfquery>
<cfif
qry_proj_ouc.RecordCount neq 0>
<cfloop
query="qry_proj_ouc">
<cfset start_dt = DateFormat(#qry_proj_ouc.SITE_OUC_BEGIN_DATE#,"DD-MMM-YYYY")>
<cfset end_dt = DateFormat(#qry_proj_ouc.SITE_OUC_END_DATE#,"DD-MMM-YYYY")>
<cfset create_dt = DateFormat(#qry_proj_ouc.CREATED_DATE#, "DD-MMM-YYYY")>
<cfset modify_dt = DateFormat(#qry_proj_ouc.MODIFIED_DATE#, "DD-MMM-YYYY")>
<!--- Trigger PROJECT_OUC_ASSIGNMENT_TRIG --->
<cfquery name="qry_insert_proj_ouc" datasource="#application.ds#">
INSERT INTO PROJECT_OUC_ASSIGNMENT
(PRJ_MASTER_ID,
OUC_ID,
SITE_OUC_BEGIN_DATE,
SITE_OUC_END_DATE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND,
COMMENT_TEXT)
VALUES
(#n_proj_master_id#,
#qry_proj_ouc.OUC_ID#,
'#start_dt#',
'#end_dt#',
'#create_dt#',
#qry_proj_ouc.CREATED_BY#,
'#modify_dt#',
#qry_proj_ouc.MODIFIED_BY#,
'#qry_proj_ouc.ACTIVE_IND#',
'#qry_proj_ouc.COMMENT_TEXT#')
</cfquery>
</cfloop>
</cfif>
<b>project ouc
assignment Created</b><br />
<!--- End of
project ouc assignment --->
<!--- check for
project tier asgmt --->
<cfquery
name="qry_chk_proj_tier" datasource="#application.ds#">
SELECT
PRJ_MASTER_ID, TIER_ID, TIER_BEGIN_DATE,
TIER_END_DATE, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, ACTIVE_IND
FROM
PROJECT_TIER_ASSIGNMENT
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
ORDER
BY TIER_ID
</cfquery>
<cfif
qry_chk_proj_tier.RecordCount neq 0>
<cfloop
query="qry_chk_proj_tier">
<cfset start_dt = DateFormat(#qry_chk_proj_tier.TIER_BEGIN_DATE#,"DD-MMM-YYYY")>
<cfset end_dt = DateFormat(#qry_chk_proj_tier.TIER_END_DATE#,"DD-MMM-YYYY")>
<cfset create_dt = DateFormat(#qry_chk_proj_tier.CREATED_DATE#, "DD-MMM-YYYY")>
<cfset modify_dt = DateFormat(#qry_chk_proj_tier.MODIFIED_DATE#, "DD-MMM-YYYY")>
<!--- Trigger PROJECT_TIER_ASSIGNMENT_TRIG --->
<cfquery name="qry_insert_proj_tier" datasource="#application.ds#">
INSERT INTO PROJECT_TIER_ASSIGNMENT
(PRJ_MASTER_ID,
TIER_ID,
TIER_BEGIN_DATE,
TIER_END_DATE,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND)
VALUES
(#n_proj_master_id#,
#qry_insert_proj_tier.TIER_ID#,
'#start_dt#',
'#end_dt#',
'#create_dt#',
#qry_insert_proj_tier.CREATED_BY#,
'#modify#',
#qry_insert_proj_tier.MODIFIED_BY#,
'#qry_insert_proj_tier.ACTIVE_IND#')
</cfquery>
</cfloop>
</cfif>
<b>project tier
assignment Created</b><br />
<!--- End of
project tier assignment --->
<!--- Check
actual cost --->
<cfquery
name="qry_chk_actual_cost" datasource="#application.ds#">
SELECT
WBS_ACTUAL_COST_ID, PRJ_MASTER_ID, WBS_TEMPLATE_HEADER_ID,
WBS_ACTUAL_COST_AMT, WBS_ACTUAL_COST_MONTH, CREATED_DATE,
CREATED_BY, MODIFIED_DATE, MODIFIED_BY, ACTIVE_IND,
COMMENT_TEXT, STATUS_LOOKUP_ID, WBS_ACTUAL_COST_USD,
INVOICED_DATE
FROM
WBS_ACTUAL_COST
WHERE
PRJ_MASTER_ID = <cfqueryparam value="#project_id#">
ORDER
BY WBS_ACTUAL_COST_ID
</cfquery>
<cfif
qry_chk_actual_cost.RecordCount neq 0>
<cfloop
query="qry_chk_actual_cost">
<!--- create wbs actual cost id --->
<cfquery name="seq_wbs_act" datasource="#APPLICATION.DS#">
select WBS_ACTUAL_COST_SEQ.nextval actual_id from dual
</cfquery>
<cfset n_actual_id = actual_id.actual_id>
actual cost id #n_actual_id# <br />
<cfset invoice_dt =
DateFormat(#qry_chk_actual_cost.INVOICED_DATE#,"DD-MMM-YYYY")>
<cfset create_dt = DateFormat(#qry_chk_actual_cost.CREATED_DATE#,
"DD-MMM-YYYY")>
<cfset modify_dt = DateFormat(#qry_chk_actual_cost.MODIFIED_DATE#,
"DD-MMM-YYYY")>
<!--- No Trigger --->
<cfquery name="qry_insert_actual_cost" datasource="#application.ds#">
INSERT INTO WBS_ACTUAL_COST
(WBS_ACTUAL_COST_ID,
PRJ_MASTER_ID,
WBS_TEMPLATE_HEADER_ID,
WBS_ACTUAL_COST_AMT,
WBS_ACTUAL_COST_MONTH,
CREATED_DATE,
CREATED_BY,
MODIFIED_DATE,
MODIFIED_BY,
ACTIVE_IND,
COMMENT_TEXT,
STATUS_LOOKUP_ID,
WBS_ACTUAL_COST_USD,
INVOICED_DATE)
VALUES
(#n_actual_id#,
#n_proj_master_id#,
#qry_insert_actual_cost.WBS_TEMPLATE_HEADER_ID#,
#qry_insert_actual_cost.WBS_ACTUAL_COST_AMT#,
#qry_insert_actual_cost.WBS_ACTUAL_COST_MONTH#,
'#create_dt#',
#qry_insert_actual_cost.CREATED_BY#,
'#modify_dt#',
#qry_insert_actual_cost.MODIFIED_BY#,
'#qry_insert_actual_cost.ACTIVE_IND#',
'#qry_insert_actual_cost.COMMENT_TEXT#',
#qry_insert_actual_cost.STATUS_LOOKUP_ID#,
#qry_insert_actual_cost.WBS_ACTUAL_COST_USD#,
'#invoice_dt#')
</cfquery>
</cfloop>
</cfif>
<b>wbs actual
cost Created</b><br />
<!--- End of
wbs actual cost --->
</cfloop>
</cfif>
<!--- if not this project id work good --->
<cfelse>
ok - #qry_chck_projid.prj_master_id# -
#qry_chck_projid.Proj_Count#
<br>
</cfif>
</cfif>
</cfloop>
<cfelse>
No Record for Archived project
</cfif>
</cfoutput>
_______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
