explicit SQL declaration of the procedure

1 files changed, 90 insertions, 4 deletions

diff --git a/bin/oxfordshire/open311_service_request.cgi b/bin/oxfordshire/open311_service_request.cgi
index caa94d855..8d89ad4ff 100755
--- a/bin/oxfordshire/open311_service_request.cgi
+++ b/bin/oxfordshire/open311_service_request.cgi
@@ -48,8 +48,9 @@ foreach (get_pem_field_names()) {
     } else {
         push @PEM_BOUND_VAR_NAMES, $_;
     }
+    
 }
-my $PEM_BOUND_VARS_DECLARATION = join ',',  @PEM_BOUND_VAR_NAMES;
+my $PEM_BOUND_VARS_DECLARATION = ""; # not used
 
 # if there's a config file, pull the password from it
 #     (as a courtesy: not be keeping the config (e.g. password) in this script, 
@@ -133,8 +134,8 @@ if ($TEST_SERVICE_DISCOVERY) {
 } elsif ($req -> param('services')) {
     get_service_discovery($req);
 } else {
-    # get_FAKE_INSERT($req); # allow a GET to make an insert, for testing (from the commandnd line!)
-    get_service_requests($req);
+    get_FAKE_INSERT($req); # allow a GET to make an insert, for testing (from the commandnd line!)
+    # get_service_requests($req);
 }
 
 #----------------------------------------------------
@@ -272,7 +273,92 @@ sub insert_into_pem {
     my $status = $$h{$F{STATUS}}; 
 
     my $service_code = $$h{$F{SERVICE_CODE}}; 
-    my $sth = $dbh->prepare("BEGIN $STORED_PROC_NAME($PEM_BOUND_VARS_DECLARATION); END;");
+    my $sth = $dbh->prepare(q#BEGIN 
+    Pem.create_enquiry
+    (ce_cat IN VARCHAR2 DEFAULT NVL(Hig.get_useopt('ENQCAT', USER),Hig.get_sysopt('ENQCAT'))
+    , ce_class IN VARCHAR2 DEFAULT NVL(Hig.get_useopt('ENQCLASS', USER),Hig.get_sysopt('ENQCLASS'))
+    , ce_title IN VARCHAR2 DEFAULT NULL
+    , ce_contact_title IN VARCHAR2 DEFAULT NULL
+    , ce_forename IN VARCHAR2 DEFAULT NULL
+    , ce_surname IN VARCHAR2 DEFAULT NULL
+    , ce_contact_type IN VARCHAR2 DEFAULT NVL(Hig.get_useopt('ENQCONTYPE', USER)
+    ,Hig.get_sysopt('ENQCONTYPE'))
+    , ce_flag IN VARCHAR2 DEFAULT 'Y'
+    , ce_pcode_id IN VARCHAR2 DEFAULT NULL
+    , ce_postcode IN VARCHAR2 DEFAULT NULL
+    , ce_building_no IN VARCHAR2 DEFAULT NULL
+    , ce_building_name IN VARCHAR2 DEFAULT NULL
+    , ce_street IN VARCHAR2 DEFAULT NULL
+    , ce_locality IN VARCHAR2 DEFAULT NULL
+    , ce_town IN VARCHAR2 DEFAULT NULL
+    , ce_county IN VARCHAR2 DEFAULT NULL
+    , ce_organisation IN VARCHAR2 DEFAULT NULL
+    , ce_work_phone IN VARCHAR2 DEFAULT NULL
+    , ce_email IN VARCHAR2 DEFAULT NULL
+    , ce_location IN VARCHAR2 DEFAULT NULL
+    , ce_description IN VARCHAR2 DEFAULT NULL
+    , ce_enquiry_type IN VARCHAR2 DEFAULT NVL(Hig.get_useopt('ENQDEFENQT', USER)
+    ,Hig.get_sysopt('ENQDEFENQT'))
+    , ce_source IN VARCHAR2 DEFAULT NVL(Hig.get_useopt('ENQDEFSRCE', USER)
+    ,Hig.get_sysopt('ENQDEFSRCE'))
+    , ce_incident_datetime DATE
+    , ce_cpr_id IN VARCHAR2 DEFAULT NULL
+    , ce_asset_id IN VARCHAR2 DEFAULT NULL
+    , ce_rse_he_id IN VARCHAR2 DEFAULT NULL
+    , ce_x IN NUMBER DEFAULT NULL
+    , ce_y IN NUMBER DEFAULT NULL
+    , ce_date_expires IN DATE DEFAULT NULL
+    , ce_doc_reference IN VARCHAR2 DEFAULT NULL
+    , ce_issue_number IN NUMBER DEFAULT NULL
+    , ce_category IN VARCHAR2 DEFAULT NULL
+    , ce_status_date IN DATE DEFAULT NULL
+    , ce_resp_of IN VARCHAR2 DEFAULT NULL
+    , ce_compl_ack_flag IN VARCHAR2 DEFAULT NULL
+    , ce_compl_ack_date IN DATE DEFAULT NULL
+    , ce_compl_flag IN VARCHAR2 DEFAULT NULL
+    , ce_compl_peo_date IN DATE DEFAULT NULL
+    , ce_compl_target IN DATE DEFAULT NULL
+    , ce_compl_complete IN DATE DEFAULT NULL
+    , ce_compl_referred_to IN VARCHAR2 DEFAULT NULL
+    , ce_compl_police_notif_flag IN VARCHAR2 DEFAULT 'N'
+    , ce_compl_date_police_notif IN VARCHAR2 DEFAULT NULL
+    , ce_compl_from IN DATE DEFAULT NULL
+    , ce_compl_to IN DATE DEFAULT NULL
+    , ce_compl_claim IN VARCHAR2 DEFAULT NULL
+    , ce_compl_corresp_date IN DATE DEFAULT NULL
+    , ce_compl_corresp_deliv_date IN DATE DEFAULT NULL
+    , ce_compl_no_of_petitioners IN NUMBER DEFAULT NULL
+    , ce_compl_remarks IN VARCHAR2 DEFAULT NULL
+    , ce_compl_cause IN VARCHAR2 DEFAULT NULL
+    , ce_compl_injuries IN VARCHAR2 DEFAULT NULL
+    , ce_compl_damage IN VARCHAR2 DEFAULT NULL
+    , ce_compl_action IN VARCHAR2 DEFAULT NULL
+    , ce_compl_litigation_flag IN VARCHAR2 DEFAULT 'N'
+    , ce_compl_litigation_reason IN VARCHAR2 DEFAULT NULL
+    , ce_compl_claim_no IN VARCHAR2 DEFAULT NULL
+    , ce_compl_determination IN VARCHAR2 DEFAULT NULL
+    , ce_compl_est_cost IN NUMBER DEFAULT NULL
+    , ce_compl_adv_cost IN NUMBER DEFAULT NULL
+    , ce_compl_act_cost IN NUMBER DEFAULT NULL
+    , ce_compl_follow_up1 IN DATE DEFAULT NULL
+    , ce_compl_follow_up2 IN DATE DEFAULT NULL
+    , ce_compl_follow_up3 IN DATE DEFAULT NULL
+    , ce_compl_insurance_claim IN VARCHAR2 DEFAULT NULL
+    , ce_compl_summons_received IN VARCHAR2 DEFAULT NULL
+    , ce_compl_user_type IN VARCHAR2 DEFAULT 'USER'
+    , ce_hct_vip IN VARCHAR2 DEFAULT NULL
+    , ce_hct_home_phone IN VARCHAR2 DEFAULT NULL
+    , ce_hct_mobile_phone IN VARCHAR2 DEFAULT NULL
+    , ce_hct_fax IN VARCHAR2 DEFAULT NULL
+    , ce_had_sub_build_name_no IN VARCHAR2 DEFAULT NULL
+    , ce_had_property_type IN VARCHAR2 DEFAULT NULL
+    , ce_status_code IN VARCHAR2 DEFAULT NULL
+    , ce_date_time_arrived IN DATE DEFAULT NULL
+    , ce_reason_late IN VARCHAR2 DEFAULT NULL
+    , error_value OUT NUMBER
+    , error_product OUT VARCHAR2
+    , ce_doc_id OUT NUMBER);
+    END;#);
 
     my %bindings = map {($_, undef)} @PEM_BOUND_VAR_NAMES;