Coding tutorials - Sql *Procedure
DECLARE
v_account_num bsnl_deposit_interest.account_num%Type;
v_tot_rebate bsnl_deposit_interest.rebate%Type;
v_depo_ob bsnl_deposit_interest.deposit_amount%Type;
v_depo_cb bsnl_deposit_interest.deposit_amount%Type;
v_tot_accchrg bsnl_deposit_interest.acc_charges%Type;
v_dep_status bsnl_servicelevel_deposit_dtls.prospect_status%Type;
v_max_seq bsnl_deposit_interest.sequence_id%Type;
i_count number default 0 ;
cursor boss is
SELECT DISTINCT account_num FROM bsnl_deposit_interest WHERE REBATE>0 and deposit_id='2'
/*and account_num='1000971357'*/
HAVING COUNT(*)>1
GROUP BY ACCOUNT_NUM,ORDER_ID ,TELEPHONE_NO,DEPOSIT_ID,SEQUENCE_ID,DEPOSIT_AMOUNT,
INTEREST_ON_DEPOSIT,REBATE,ACC_CHARGES,START_DT,END_DT,INTEREST_CALCULATION_DT,EXECUTION_STATUS,ACCOUNT_TYPE,REC_MD_DT
;
BEGIN
for x in boss
loop
begin
v_account_num :=x.account_num;
select prospect_status into v_dep_status from bsnl_servicelevel_deposit_dtls where account_num=v_account_num and deposit_id='2';
SELECT deposit_amount into v_depo_ob FROM BSNL_DEPOSIT_INTEREST WHERE ACCOUNT_NUM=v_account_num and deposit_id='2' and sequence_id='1';
SELECT max(sequence_id) into v_max_seq FROM BSNL_DEPOSIT_INTEREST WHERE ACCOUNT_NUM=v_account_num and deposit_id='2';
SELECT deposit_amount into v_depo_cb FROM BSNL_DEPOSIT_INTEREST WHERE ACCOUNT_NUM=v_account_num and deposit_id='2' and sequence_id=v_max_seq;
SELECT sum(rebate) into v_tot_rebate FROM BSNL_DEPOSIT_INTEREST WHERE ACCOUNT_NUM=v_account_num and deposit_id='2';
select sum(acc_charges) into v_tot_accchrg FROM BSNL_DEPOSIT_INTEREST WHERE ACCOUNT_NUM=v_account_num and deposit_id='2';
if v_dep_status<>'R' -----refunded accounts are eliminated
then
if round(v_depo_ob-v_depo_cb)<>round(v_tot_rebate+v_tot_accchrg,0)
then
DBMS_OUTPUT.put_line (v_account_num ||','||v_depo_ob||','||v_tot_rebate||','||v_tot_accchrg||','||v_depo_cb);
i_count:=i_count+1;
end if;
end if;
EXCEPTION
WHEN others THEN DBMS_OUTPUT.put_line (v_account_num);
end;
end loop;
dbms_output.put_line(i_count);
END;
|
Feature |
SQL |
PL/SQL |
|
Definition |
SQL is a
standard language for managing and manipulating relational databases. |
PL/SQL
is Oracle's procedural extension to SQL, allowing for procedural programming
capabilities within SQL. |
|
Type |
Declarative
language |
Procedural
language |
|
Purpose |
Used for
querying, updating, inserting, and deleting data in a database. |
Used for
writing complex programs that involve SQL statements, control structures, and
procedural logic. |
|
Structure |
Consists
of simple SQL statements (e.g., SELECT, INSERT, UPDATE, DELETE). |
Consists
of blocks of code that can contain SQL statements, variable declarations,
loops, conditions, and error handling. |
|
Execution |
SQL
statements are executed one at a time. |
PL/SQL
code blocks can be executed in one go, allowing for multiple SQL statements
to run together. |
|
Control
Structures |
Does not
support control structures (e.g., loops, conditional statements). |
Supports
control structures (e.g., IF, CASE, FOR, WHILE) allowing for conditional
logic and looping. |
|
Error
Handling |
Limited
error handling; does not provide explicit mechanisms for error management. |
Has
robust error handling capabilities using exceptions, allowing developers to
manage errors gracefully. |
|
Performance |
Efficient
for simple queries; however, performance may degrade with complex operations
due to multiple round-trips to the database. |
More
efficient for complex operations as it minimizes the number of database calls
by executing multiple SQL statements in a single block. |
|
Variable
Declaration |
Does not
support variable declaration. |
Supports
the declaration of variables and constants, enabling dynamic programming
capabilities. |
|
Usage
Context |
Used for
direct database operations. |
Used for
creating stored procedures, functions, triggers, and packages. |
|
Result
Sets |
Returns
result sets directly (e.g., from SELECT statements). |
Can
return results through output parameters in stored procedures and functions. |
|
Database
Dependency |
Standard
SQL is used across various database systems (e.g., MySQL, PostgreSQL, SQL
Server). |
PL/SQL
is specific to Oracle databases; similar procedural languages exist in other
databases (e.g., T-SQL in SQL Server, PL/pgSQL in PostgreSQL). |
In
PL/SQL, a trigger is a stored procedure that is automatically executed, or
"triggered," by the database when a specific event occurs. Triggers
are commonly used to enforce complex business rules, audit changes, or
automatically respond to changes in the database.
# Key
Concepts of PL/SQL Triggers
1. Automatic
Execution:
- Triggers run automatically in response to
events on a table or view, such as `INSERT`, `UPDATE`, or `DELETE`.
2. Event-Based
Logic:
- A trigger can be set to activate before or
after an event on a table.
- It can also be defined to fire for each
row affected by the event or just once per SQL statement.
3. Types
of Triggers:
- DML Triggers: Triggered by Data
Manipulation Language (DML) operations like `INSERT`, `UPDATE`, or `DELETE`.
- DDL Triggers: Triggered by Data Definition
Language (DDL) operations like `CREATE`, `ALTER`, or `DROP`.
- INSTEAD OF Triggers: Used on views to handle
`INSERT`, `UPDATE`, or `DELETE` operations that cannot be performed directly.
- Compound Triggers: Special triggers that
consolidate multiple timing points (like `BEFORE`, `AFTER`) in a single
structure, particularly useful for managing complex row-level processing.
4. Structure
of a Basic DML Trigger:
```sql
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
DECLARE
-- Variable declarations
BEGIN
--
Trigger logic (PL/SQL code)
END;
```
- BEFORE or AFTER specifies whether the
trigger should fire before or after the event.
- FOR EACH ROW specifies that the trigger
should execute once for each row affected (row-level trigger).
- Without `FOR EACH ROW`, the trigger is
statement-level, executing only once per SQL statement.
5. OLD
and NEW Keywords:
- Within a row-level trigger, the `:OLD` and
`:NEW` keywords allow access to column values before and after the DML event.
- `:OLD.column_name` refers to the value
before the event.
- `:NEW.column_name` refers to the value
after the event (inserts and updates only).
6. Example:
```sql
CREATE OR REPLACE TRIGGER trg_audit_employee
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id,
action, change_date)
VALUES (:NEW.employee_id, 'INSERT or
UPDATE', SYSDATE);
END;
```
This trigger records an audit entry in the `employee_audit` table every time a row is inserted or updated in the `employees` table.
# Uses
of Triggers
- Data
Auditing: Logging changes to data for tracking history.
- Data
Validation: Enforcing business rules before data is committed.
- Derived
Columns: Automatically calculating or updating values.
- Security: Controlling access to table rows based on complex logic.
Triggers
in PL/SQL can enhance data integrity and automate common database tasks, but
they should be used with caution to avoid performance impacts, especially when
dealing with high-transaction tables.
====
PL/SQL, the procedural
extension of SQL for Oracle databases, offers a variety of data types, allowing
developers to handle different kinds of data effectively. Here’s a breakdown of
key data types in PL/SQL:
1. Scalar Data Types
Scalar data types hold a single value. These
include:
- Numeric Types:
- `NUMBER(p, s)`: Used for fixed and
floating-point numbers with precision `p` and scale `s`.
- `INTEGER`, `INT`, `SMALLINT`: Variants
of `NUMBER` typically for whole numbers.
- `BINARY_FLOAT`, `BINARY_DOUBLE`:
Represent single and double-precision floating-point numbers, respectively.
- Character Types:
- `CHAR(n)`: Fixed-length character
string, padded with spaces up to `n` characters.
- `VARCHAR2(n)`: Variable-length character
string up to `n` characters (most commonly used).
- `NCHAR(n)`, `NVARCHAR2(n)`: Character
strings that support Unicode data, with fixed and variable lengths.
- Date/Time Types:
- `DATE`: Stores date and time to the
second, including day, month, year, hours, minutes, and seconds.
- `TIMESTAMP`: Extends `DATE` to include
fractional seconds.
- `TIMESTAMP WITH TIME ZONE`, `TIMESTAMP
WITH LOCAL TIME ZONE`: Store timestamps with timezone information.
- Boolean Type:
- `BOOLEAN`: Holds `TRUE`, `FALSE`, or
`NULL` values. Commonly used in control statements, though not directly
storable in the database.
2. Composite Data Types
Composite types allow grouping of multiple
values. These are helpful for managing collections of related data.
- Records:
- `RECORD`: Allows defining a custom
structure with multiple fields of different types. Useful for handling row
structures.
```plsql
TYPE employee_record IS
RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100),
emp_salary NUMBER
);
```
- Collections:
- Associative Arrays (formerly called
index-by tables): Arrays with key-value pairs, where the keys are not
consecutive integers.
- Nested Tables: A one-dimensional array
that can store a variable number of elements, can be stored in the database.
- VARRAYs (Variable-Size Arrays): Similar
to nested tables but have a maximum size and must be consecutive elements.
```plsql
TYPE num_array IS VARRAY(10) OF NUMBER;
```
3. LOB (Large Object) Data Types
LOBs are used for storing large amounts of
data, such as images or documents.
- `BLOB`: Binary Large Object, used to store
binary data like images, audio, etc.
- `CLOB`: Character Large Object, used for
large text data.
- `NCLOB`: CLOB supporting Unicode.
- `BFILE`: Pointer to a file stored outside
the database, like on the server's filesystem.
4. Reference Data Types
Used to store pointers or references to
database objects.
- REF CURSOR: A pointer to a result set
returned by a query. Often used in procedures and functions to pass query
results.
- SYS_REFCURSOR: A predefined weak `REF
CURSOR` that can be used without explicit type declaration.
5. User-Defined Data Types
PL/SQL also allows defining custom types,
which can be based on existing types or combinations of types.
- User-Defined Records: Allows creating a
custom structured type with fields.
- User-Defined Collections: Using the
`TABLE` or `VARRAY` types, you can define custom collections of a specific
type.
6. Other Special Types
- RAW: Used to store binary or byte-oriented
data, primarily for data that should not be converted to a character set.
- UROWID: A universal row identifier used for identifying rows in tables with a primary key.
Each of these data
types in PL/SQL allows precise control over data, enabling efficient
manipulation, storage, and retrieval in Oracle databases.
===
The PL/SQL compilation process converts PL/SQL code (like procedures, functions, and triggers) into an executable form that Oracle can process. It consists of several stages: syntax checking, semantic analysis, code generation, and storing the compiled code in the database.
Stages of the PL/SQL Compilation Process
1. Syntax Checking:
- Purpose: Ensures that the PL/SQL code
follows correct syntax rules.
- Process: Oracle checks for syntactical
errors like missing semicolons, misspelled keywords, or improper variable
declarations.
- Outcome: If there are syntax errors, the process stops here, and Oracle returns an error to the developer with information about the issue. If no errors are found, it moves to the next stage.
2. Semantic Analysis:
- Purpose: Ensures that the code makes
logical sense and adheres to PL/SQL semantics.
- Process: Oracle checks for issues like:
- Variable declarations that do not match
the types used.
- References to non-existent tables,
columns, procedures, or functions.
- Ensures that the PL/SQL logic is
meaningful and valid.
- Outcome: If any semantic issues are detected (like using an undeclared variable or calling a procedure that doesn’t exist), Oracle returns an error with details. Otherwise, it proceeds to the code generation phase.
3. Code Generation:
- Purpose: Translates the syntactically and
semantically valid PL/SQL code into a form that can be stored and executed.
- Process: The PL/SQL compiler generates p-code
(an intermediate, portable form of code) rather than machine-level code. P-code
is interpreted by the Oracle PL/SQL engine at runtime.
- Outcome: The p-code is a platform-independent representation of the PL/SQL program, optimized for efficient execution in the Oracle environment.
4. Storing Compiled
Code:
- Purpose: Saves the compiled code in the
database to be executed when required.
- Process: The generated p-code is stored in
Oracle’s system catalog, making it available for immediate or future execution.
- Outcome: When the code is successfully
stored, it is considered a valid object (e.g., a valid procedure or function)
and is ready for execution.
5. Execution and
Runtime Checking (Post-Compilation):
- When the PL/SQL block is executed, the
Oracle PL/SQL engine interprets the stored p-code.
- Runtime Errors: Even if the code compiles successfully, some errors might only appear during execution, such as divide-by-zero errors, `NO_DATA_FOUND`, `TOO_MANY_ROWS`, etc. These are handled using PL/SQL’s exception handling mechanisms.
Example: Compilation Feedback and Dependencies
During compilation, Oracle checks dependencies (like tables or other PL/SQL code) referenced in the PL/SQL code. If any of these dependencies change (such as a table structure modification), the code may become invalid. In this case, Oracle marks it for recompilation, which will happen automatically when the code is called next.
Summary of PL/SQL Compilation Outcomes
- Syntax and Semantic
Errors: Detected during compilation; must be resolved for successful
compilation.
- Invalid State: If
dependencies change, the PL/SQL code may need recompilation.
- Runtime Errors: Handled using exception handling, as they only occur during actual execution.
This compilation
process ensures that PL/SQL code is optimized, syntactically correct, logically
consistent, and ready for execution in the Oracle environment.
===
PL/SQL packages are
powerful tools in Oracle that bundle related procedures, functions, variables,
and other PL/SQL constructs into a single unit. Using packages provides a
number of benefits for performance, organization, security, and modularity in
database applications. Here’s an overview of the key benefits:
1. Modularity and Code Organization
- Purpose: Packages allow grouping of
related procedures, functions, variables, and constants in a single place,
making code more organized and modular.
- Benefit: By logically grouping related
items, packages help maintain clean and readable code, which makes development,
debugging, and maintenance easier.
2. Encapsulation and Security
- Purpose: Packages provide a way to hide
specific procedures and functions by defining them as private (only accessible
within the package).
- Benefit: Sensitive data and internal
implementation details can be hidden, providing a controlled interface for
other applications and users. Only the package’s public (specification) part is
visible to users, while private components (defined in the package body) remain
hidden and inaccessible from outside.
3. Improved Performance
- Purpose: Packages improve performance
through reduced I/O and better memory management.
- Benefit:
- Loading Efficiency: When a package is
first accessed, the entire package is loaded into memory. Subsequent calls to
any other parts of the package reuse this already loaded code, saving time.
- Reduced Network Traffic: Packages
minimize network traffic by bundling multiple related procedures/functions
together, reducing the need to repeatedly call separate standalone
procedures/functions.
4. Reduced Redundancy and Reusability
- Purpose: Packages allow you to reuse code
across applications by placing common procedures and functions in one package
that can be accessed by various programs.
- Benefit: Reusability reduces redundancy
and saves development time. Changes to common logic need to be made only in one
place, and all applications using the package will reflect the updates
automatically.
5. Overloading Support
- Purpose: Packages support overloading,
which means you can define multiple procedures or functions with the same name
but with different parameter lists.
- Benefit: Overloading provides flexibility
and allows for polymorphism within PL/SQL, making the package easier to use in
different contexts with varying inputs. It also allows for simplified
interfaces for users, as they don’t have to remember multiple function names
for similar actions.
6. Easier Maintenance and Version Control
- Purpose: Packages allow code maintenance
and version control without disrupting applications.
- Benefit: Since the package specification
(interface) and body (implementation) are separate, changes can be made to the
package body without affecting the specification. As long as the interface
remains the same, dependent applications are not affected. This separation
allows for safer updates and bug fixes.
7. Automatic Dependency Management
- Purpose: Packages track dependencies
automatically within the database.
- Benefit: When a package relies on other
database objects (like tables or views), Oracle manages these dependencies and
automatically revalidates or recompiles them as needed, ensuring reliability
and reducing error handling on the developer's part.
8. Session-Scoped Global Variables
- Purpose: Packages allow defining global
variables that persist for the duration of a session.
- Benefit: These global variables can
maintain session-specific information, such as settings or counters, that can
be accessed across multiple package calls within the same session, allowing for
greater state management capabilities.
Example Structure of a Package
A package in PL/SQL has
two parts: the specification and the body.
- Specification:
Declares the public procedures, functions, types, and variables available to
users.
- Body: Contains the
code for the procedures and functions defined in the specification, as well as
any private elements.
```
plsql-- Package
Specification
CREATE OR REPLACE
PACKAGE Employee_Pkg AS
PROCEDURE Add_Employee(emp_id NUMBER,
emp_name VARCHAR2);
FUNCTION Get_Employee(emp_id NUMBER) RETURN
VARCHAR2;
END Employee_Pkg;
-- Package Body
CREATE OR REPLACE
PACKAGE BODY Employee_Pkg AS
PROCEDURE Add_Employee(emp_id NUMBER,
emp_name VARCHAR2) IS
BEGIN
-- Procedure implementation
END Add_Employee;
FUNCTION Get_Employee(emp_id NUMBER) RETURN
VARCHAR2 IS
BEGIN
-- Function implementation
RETURN 'Employee Name';
END Get_Employee;
END Employee_Pkg;
```
In this example, the
`Employee_Pkg` package allows easy access to `Add_Employee` and `Get_Employee`
functionalities, while the implementation can be modified without changing the
calling code.
Summary of Benefits :PL/SQL packages provide modularity, encapsulation, performance benefits, code reusability, and ease of maintenance, making them an essential feature in Oracle applications for building complex, scalable, and maintainable code.
===
different uses of
stored procedures:
Stored procedures in
PL/SQL are powerful tools that allow database developers to encapsulate logic
within the database itself. They are reusable, precompiled, and can handle
complex tasks efficiently. Here are some of the primary uses and benefits of
stored procedures:
1. Data Validation and Business Rules
Enforcement
- Purpose: Ensure data integrity by
implementing complex validation and business logic directly in the database.
- Use Case: A stored procedure can enforce
validation rules for new data entries, such as checking if a value falls within
an acceptable range or validating that related records exist.
- Example: Before inserting a new employee
record, a stored procedure can check that the department ID exists and the
salary is within the allowed range for that department.
2. Encapsulation of Complex Operations
- Purpose: Encapsulate complex queries and
data manipulation logic to simplify application development.
- Use Case: Instead of writing complex SQL
in each application layer, you can call a stored procedure that performs these
operations internally.
- Example: A stored procedure can generate
an invoice by aggregating data from various tables, calculating totals, and
applying discounts in a single transaction.
3. Improved Performance and Reduced Network
Traffic
- Purpose: Minimize network traffic and
improve performance by moving data processing logic to the database server.
- Use Case: Stored procedures allow large
data processing tasks to be handled directly in the database, reducing the
amount of data that needs to be sent over the network to an application.
- Example: A stored procedure can process
and analyze large datasets within the database, sending only the final result
to the application, thus saving bandwidth and reducing response times.
4. Reusable Code for Consistency and
Maintainability
- Purpose: Centralize business logic and
make it reusable across multiple applications or modules.
- Use Case: A stored procedure can
standardize calculations, transformations, and validations, reducing code
duplication and making updates easier.
- Example: A stored procedure for
calculating employee bonuses can be reused in different applications, ensuring
that the bonus calculation is consistent.
5. Automating Administrative and Maintenance
Tasks
- Purpose: Automate routine administrative
tasks, such as backups, log cleanup, and data archiving.
- Use Case: Scheduling stored procedures as
part of maintenance jobs can automate these tasks and reduce manual
intervention.
- Example: A stored procedure can be
scheduled to delete old log entries or archive data to another table at regular
intervals.
6. Transactional Integrity and Error Handling
- Purpose: Group multiple SQL operations
into a single unit with transaction control, ensuring either complete success
or rollback.
- Use Case: By using stored procedures,
developers can enforce ACID properties in transactions and handle errors
consistently.
- Example: A stored procedure that transfers
money between two accounts can ensure that both the debit and credit operations
succeed or are rolled back if an error occurs in either.
7. Enhanced Security and Access Control
- Purpose: Restrict direct access to
database tables by providing controlled access through stored procedures.
- Use Case: Stored procedures allow
developers to enforce user access permissions by exposing only the operations
required by specific user roles.
- Example: Instead of giving users direct access
to the `employee` table, a stored procedure allows users to access employee
data through approved methods, protecting sensitive data like salaries.
8. Interfacing with Applications and APIs
- Purpose: Act as an interface between
applications and the database, abstracting database complexity.
- Use Case: Applications can call stored
procedures without needing to know the database schema or SQL details, making
changes to the database schema less disruptive to applications.
- Example: An application might call a
stored procedure to place an order, which then handles all the necessary steps
to update stock, generate an invoice, and record the transaction in the
database.
9. Dynamic SQL and Flexibility in Query
Execution
- Purpose: Use dynamic SQL within stored
procedures to create flexible query execution.
- Use Case: For scenarios where the SQL
query must change based on inputs, stored procedures with dynamic SQL allow
constructing and executing SQL at runtime.
- Example: A stored procedure can
dynamically generate a SELECT query based on user-selected filters, making it
flexible enough to handle different report types.
10. Auditing and Logging Activities
- Purpose: Record user activities and
maintain an audit trail for sensitive data changes.
- Use Case: By using stored procedures,
developers can add logging to record who accessed or modified data, along with
timestamps.
- Example: A stored procedure that updates
employee information can automatically insert a record in an audit table,
tracking changes made to sensitive data fields.
11. Batch Processing and Data Migration
- Purpose: Facilitate batch jobs and
large-scale data migrations between tables or databases.
- Use Case: For ETL (Extract, Transform,
Load) processes, stored procedures can handle large data operations
efficiently.
- Example: A stored procedure can read data from a staging table, apply necessary transformations, and insert it into a target table, ensuring data integrity and efficient processing.
Summary
Stored procedures
provide a centralized, secure, and high-performing way to manage data
operations and business logic directly within the database. They improve
maintainability, support modular programming, enable reusability, reduce
network overhead, and enhance security, making them essential for building
robust, scalable database-driven applications.
===
#include "BSNL_DEE.h"
#define CLARITY 1
#define IVRS 3
#define PMS 2
//--------------- Host variable declaraction ---------------
exec sql begin declare section;
/* Cursor variables declaration */
sql_cursor g_eli_acc_cursor;
sql_cursor g_col_feed_cursor;
/* Varchar variables declaration */
VARCHAR g_bb_flag[3];
VARCHAR g_crm_dblink[20];
VARCHAR g_error_msg[ERR_MSG_LEN];
VARCHAR g_timestamp[DATE_ARR_LEN];
VARCHAR g_systemdate[DATE_ARR_LEN];
VARCHAR g_pmsfeeddate[DATE_ARR_LEN];
VARCHAR g_new_acc_num[ACC_SER_NUM_SIZE];
VARCHAR g_created_dtm[ORACLE_DATE_SIZE];
VARCHAR g_service_number[ACC_SER_NUM_SIZE];
VARCHAR g_crm_created_dtm[ORACLE_DATE_SIZE];
VARCHAR g_dun_actiontype_str[VAR_LEN_ARR_SIZE];
VARCHAR g_pms_feedfile_dir_str[VAR_LEN_ARR_SIZE];
VARCHAR g_nondun_actiontype_str[VAR_LEN_ARR_SIZE];
VARCHAR g_pms_feed_filename[VAR_LEN_ARR_SIZE];
/* int variables declaration */
int l_action_id;
int g_action_id;
int g_return_sts;
int g_action_seq;
int g_error_code = 0;
int g_bb_prod_id;
int g_bb_bar_prod_id;
int g_ogb_vip_act_id;
int g_new_action_num;
int g_request_status;
int g_pd_act_type_info;
int g_ocb_act_type_info;
int g_icb_act_type_info;
int g_ogicb_act_type_info;
int g_dab_act_id;
int g_ogdab_act_id;
/* long variables declaration */
long g_debt_age;
long g_credit_class;
long g_new_bill_seq;
long g_DEBTage_task_id;
/* char/string variables declaration */
char *g_connect_str ;
char g_instance_id[22];
char g_action_id_str[3];
char *g_dun_action_ptr;
char *g_vip_act_type_ptr;
char *g_nondun_action_ptr;
char *g_pms_feedfile_dir_ptr;
char *g_act_feedfile_dir_ptr;
/* Double variables declaration */
double g_rollup_debt;
double g_threshold_mny;
double g_outstanding_tot;
/* INDICATOR variables declaration */
short g_ind_var; /*Indicator Variable*/
//---------- Variables for generating diffrent feed files ---------
VARCHAR g_ivrs_feedfile_dir[VAR_LEN_ARR_SIZE];
VARCHAR g_clarity_feedfile_dir[VAR_LEN_ARR_SIZE];
VARCHAR g_ivrs_act_type_info[VAR_LEN_ARR_SIZE];
VARCHAR g_vip_act_type_info[VAR_LEN_ARR_SIZE];
exec sql end declare section;
exec sql include sqlca;
//--------------- Global counters to keep track of Total, Errored & Success records ---------------
long g_rec_total = 0;
long g_suc_total = 0;
long g_err_total = 0;
long g_file_rec_cnt = 0;
/*F*****************************************************
* Function for inserting ftp file in reporting directory.
*
***************************************************F*/
void insert_ftp_file ( char *ftp_filename,
long rec_cnt,
char *ext_system
)
{
int l_rtn;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR FTP_FILENAME[100];
VARCHAR EXTERNAL_SYSTEM[10];
EXEC SQL END DECLARE SECTION;
sprintf(traceBuf, "Creating entry in BSNL_CLARITY_FILE_RPT table for file %s", ftp_filename);
traceOutput(__LINE__, INF_LVL, traceBuf);
strcpy((char *)FTP_FILENAME.arr,ftp_filename );
FTP_FILENAME.len = strlen((char *)FTP_FILENAME.arr);
strcpy((char *)EXTERNAL_SYSTEM.arr,ext_system );
EXTERNAL_SYSTEM.len = strlen((char *)EXTERNAL_SYSTEM.arr);
sprintf(traceBuf, "File name '%s', rec count '%ld', extrnl system '%s'", ftp_filename, rec_cnt, ext_system);
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
:l_rtn := BSNL_COLLECTION_PACKAGE.BSNL_LOG_CLARITY_FILE_RPT( P_OPERATION_FLAG => 'C'
,p_FILENAME => :FTP_FILENAME
,P_TOTAL_RACORDS => :rec_cnt
,P_EXTERNAL_SYS => :EXTERNAL_SYSTEM
,P_ERROR_CODE => :g_error_code INDICATOR :g_ind_var
,P_ERROR_MESSAGE => :g_error_msg INDICATOR :g_ind_var);
END;
END-EXEC;
if ( l_rtn != 0 ){
sprintf(traceBuf, "Failed to create entry in BSNL_CLARITY_FILE_RPT table for file %s. %s", ftp_filename, g_error_msg.arr);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}else{
sprintf(traceBuf, "Successfully created entry in BSNL_CLARITY_FILE_RPT table for file %s ", ftp_filename);
traceOutput(__LINE__, INF_LVL, traceBuf);
return;
}
return;
}
/*F*****************************************************
* Function to search a token in string
***************************************************F*/
int tokencheck ( char *str,
int token )
{
char temp[1000];
char * pch;
strcpy(temp, str);
pch = strtok ( temp,",");
while (pch != NULL)
{
if ( atoi(pch) == token )
return 1;
pch = strtok (NULL, " ,.-");
}
return 0;
}
/*F*****************************************************
* Function for ftp a file
* Input Parameters:
* File_Name: File to be ftp.
* Oper_type: 1 Clarity Feed
* 2 CRM Feed. etc.
* returns 1: Success.
* 0: Error.
***************************************************F*/
/*int bsnl_ftp( char * File_Name
, int Oper_type )
{
char *l_Exec_qry;
int l_rtn;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR IP_Address[25];
VARCHAR UserName[25];
VARCHAR Passwd[25];
VARCHAR Dest_Dir[1024];
EXEC SQL END DECLARE SECTION;
//-- Retrieve Clarity parameters if file to FTP is Clarity feed file...
if ( Oper_type == 1 ){
EXEC SQL EXECUTE
BEGIN
:l_rtn := BSNL_COLLECTION_PACKAGE.BSNL_GET_CLARITY_PARAMS (
P_CLARITY_FTP_DIR => :Dest_Dir,
P_CLARITY_FTP_PASSWORD => :Passwd,
P_CLARITY_FTP_USERNAME => :UserName,
P_CLARITY_FTP_SERVER => :IP_Address);
END;
END-EXEC;
}
if ( l_rtn != 0 ){
sprintf(traceBuf, "Error retrieving mandatory parameters for Clarity file transfer ");
traceOutput(__LINE__, WRN_LVL, traceBuf);
return 0;
}
l_Exec_qry = (char *) malloc(strlen("ftp.pl -a ")+ IP_Address.len + UserName.len + Passwd.len + Dest_Dir.len + sizeof(File_Name)+ 5);
strcpy(l_Exec_qry, (char *)"ftp.pl -a ");
strcat(l_Exec_qry, (char *)IP_Address.arr );
strcat(l_Exec_qry, (char *)":" );
strcat(l_Exec_qry, (char *)UserName.arr );
strcat(l_Exec_qry, (char *)":" );
strcat(l_Exec_qry, (char *)Passwd.arr );
strcat(l_Exec_qry, (char *)":" );
strcat(l_Exec_qry, (char *)Dest_Dir.arr );
strcat(l_Exec_qry, (char *)":" );
strcat(l_Exec_qry, File_Name );
if ( system( l_Exec_qry ) != 0 ){
sprintf(traceBuf, "Failed to FTP file %s. Please do it manually after taking corrective Action ", File_Name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
return 0;
}else{
sprintf(traceBuf, "FTP has been ftp succssfully at location file '%s' of FTP server '%s'. ", Dest_Dir.arr, IP_Address.arr);
traceOutput(__LINE__, INF_LVL, traceBuf);
return 1;
}
}
*/
/*F*****************************************************
* Function to update ProcessInstanceLog table
* once BSNL_DEE process finishes.
***************************************************F*/
void UpdateProcessInstanceLogInfo(int l_process_sts )
{
int l_instance_id;
l_instance_id = atoi(g_instance_id);
sprintf(traceBuf, "Updating process instance now with process instance ID %d and process status %d ", l_instance_id, l_process_sts);
traceOutput(__LINE__, INF_LVL, traceBuf);
if(l_instance_id != 0)
{
sprintf(traceBuf, "total records %d, %d %d and process status %d ", g_rec_total, g_err_total, l_instance_id, l_process_sts);
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL
UPDATE PROCESSINSTANCELOG
SET WORK_DONE = :g_rec_total,
WORK_FAILED = :g_err_total,
TOTAL_ERRORS = :g_err_total,
PROCESS_STATUS = :l_process_sts
WHERE PROCESS_INSTANCE_ID = :l_instance_id;
}
}
/*F*****************************************************
* Function to update remote crm table
***************************************************F*/
void crm_table_update(){
long l_normal_tot, l_vip_tot;
char szInsertQuery [2500];
sprintf(traceBuf, "Writing Permanent disconnection records in CRM table now...");
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL WHENEVER SQLERROR GOTO CRM_FEED_ERR;
EXEC SQL EXECUTE
BEGIN
SAVEPOINT ACC_INSERT;
BSNL_COLLECTION_PACKAGE.BSNL_CRM_FEED_UPDATE( P_CRM_DBLINK => :g_crm_dblink
, P_PD_ACTION_TYPE => :g_pd_act_type_info
, P_NORMAL_TOT => :l_normal_tot INDICATOR :g_ind_var
, P_VIP_TOT => :l_vip_tot INDICATOR :g_ind_var
, P_ERROR_CODE => :g_error_code INDICATOR :g_ind_var
, P_ERROR_MESSAGE => :g_error_msg INDICATOR :g_ind_var );
END;
END-EXEC;
if ( g_error_code == 0 ) {
sprintf(traceBuf, "Successfully written '%d' Permanent disconnection records for normal customers in remote zperm table", l_normal_tot);
traceOutput(__LINE__, INF_LVL, traceBuf);
sprintf(traceBuf, "Successfully written '%d' VIP dunning records in remote zvip_dun table", l_vip_tot);
traceOutput(__LINE__, INF_LVL, traceBuf);
}else{
sprintf(traceBuf, "Error writing records in remote CRM table. %s", g_error_msg.arr);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
EXEC SQL COMMIT;
return;
CRM_FEED_ERR:
strcpy( (char *)g_error_msg.arr, "Error writing Permanent disconnection records. ");
strcat ((char *)g_error_msg.arr, sqlca.sqlerrm.sqlerrmc);
g_error_msg.len = strlen((char *)g_error_msg.arr);
g_error_code = sqlca.sqlcode;
sprintf(traceBuf, "Error inserting permanent disconnection records in CRM table. %s", g_error_msg.arr);
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ERROR_DATA(BSNL_COLLECTION_PACKAGE.G_SYSDATE,
'BSNL_DEE_CRM_FEED_ERROR',
:g_error_code,
:g_error_msg);
END;
END-EXEC;
g_error_code = 0;
return;
}
/*F*************************************************************************
* Create Collection Feed File.
* Retrieve all the records having Request Status as pending (1) in Error in export (3).
* If Action type Category II type, Category I PD, Letter generation & Collection Agency Transfer Actions types
* then insert into Category II feed file.
* Else if Action type Category I, SMS action type then insert into Category I SMS feed file.
* Else if Action type Category I, IVRS action type then insert into Category I IVRS feed file.
* Else if Action type Category I, CLARITY action type then insert into Category I CLARITY feed file.
* Update status of each record 'Exported for Processing (2).
* Close the file.
* If no record in file then delete it
* Else move the file to the desired location.
*
*************************************************************************F*/
void CreateFeedFile()
{
/*char l_cat2_file_name[150];*/
char l_cat1_ivrs_file_name[150];
char l_cat1_clarity_file_name[150];
char l_clarity_file_name[100];
char l_crm_dunning_file_name[150];
char l_clarity_done_file_name[150];
FILE *l_cat_done_fhandle;
/*FILE *l_cat2_fhandle;*/
FILE *l_cat1_ivrs_fhandle;
FILE *l_cat1_clarity_fhandle;
//FILE *l_crm_dunning_fhandle;
long l_cat2_file_rec_cnt = 0;
long l_cat1_sms_file_rec_cnt = 0;
long l_cat1_ivrs_file_rec_cnt = 0;
long l_cat1_clarity_file_rec_cnt = 0;
long l_crm_dunning_file_rec_cnt = 0;
char l_action_id_str[3];
char l_creclass_id_str[3];
char l_dest_file_name[200];
int l_fwrite_sts;
int l_rtn_sts;
EXEC SQL WHENEVER SQLERROR GOTO LOCAL_ERR1;
EXEC SQL SAVEPOINT COL_FILE_SAVEPOINT;
EXEC SQL ALLOCATE :g_col_feed_cursor;
//------ Excluding elgible P2P record -----
sprintf(traceBuf, "Updating Promise2Pay customers... ");
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
BSNL_COLLECTION_PACKAGE.BSNL_P2P_MAIN ( P_ERROR_CODE => :g_error_code
, P_ERROR_MSG => :g_error_msg );
END;
END-EXEC;
sprintf(traceBuf, "Promise2Pay successfully completed... ");
traceOutput(__LINE__, WRN_LVL, traceBuf);
crm_table_update();/*To be uncommented once need to automate this process ...*/
//------- If any error in process then skip file generation process
/* if ( g_error_code != 0 ){
sprintf(traceBuf, "%s. Feed file generation process skipping' ", g_error_msg.arr);
traceOutput(__LINE__, WRN_LVL, traceBuf);
return;
}
*/
//------ Retrieving data to be exported -----------
EXEC SQL EXECUTE
BEGIN
OPEN :g_col_feed_cursor FOR
SELECT ACCOUNT_NUM, DRIVING_BILL_SEQ,ACTION_NUMBER,ACTION_SEQ,ACTION_ID,
CREDIT_CLASS_ID,TO_CHAR(CREATED_DTM,'DD-MON-YYYY'),TO_CHAR(CREATED_DTM,'DDMMYYYY'),
REQUEST_STATUS, OUTSTANDING_DEBT_TOT,CHASE_DEBT_TOT,ACTION_PROCESSING_NUMBER, BROADBAND_FLAG
FROM BSNL_DEBTESCALATIONREQUEST
WHERE REQUEST_STATUS = 1;//in (1,3);
END;
END-EXEC;
//------------- Creating all collection feed files -------
/*sprintf( l_cat2_file_name, "%s/COL_CAT2_FEED_%s.txt", g_catII_feedfile_dir.arr, g_timestamp.arr);
l_cat2_fhandle = fopen(l_cat2_file_name,"w+");
*/
sprintf( l_clarity_file_name, "dunning_%s.data", g_timestamp.arr);
sprintf( l_cat1_clarity_file_name, "%s/%s", g_clarity_feedfile_dir.arr, l_clarity_file_name);
l_cat1_clarity_fhandle = fopen(l_cat1_clarity_file_name,"w+");
sprintf( l_cat1_ivrs_file_name, "%s/ivrs_dunning_%s.txt", g_ivrs_feedfile_dir.arr, g_timestamp.arr);
l_cat1_ivrs_fhandle = fopen(l_cat1_ivrs_file_name,"w+");
if ( /*l_cat2_fhandle == NULL ||*/ l_cat1_ivrs_fhandle == NULL || l_cat1_clarity_fhandle == NULL )
{
EXEC SQL EXECUTE
BEGIN
:g_error_msg := 'Error opening Collection feed file. Feed file creation step skipped.';
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ERROR_DATA(BSNL_COLLECTION_PACKAGE.G_SYSDATE,
'BSNL_DEE_COL_FEED_FILE_WRITE_PROCESS',
1,
:g_error_msg);
END;
END-EXEC;
sprintf(traceBuf, "Error opening Collection feed file. Feed file creation step skipped. Error is %s",strerror(errno));
traceOutput(__LINE__, ERR_LVL, traceBuf);
return;
}
sprintf(traceBuf, "Following Collection feed files created successfully.");
traceOutput(__LINE__, INF_LVL, traceBuf);
/*sprintf(traceBuf, "Category II Collection feed file %s.", l_cat2_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);*/
sprintf(traceBuf, "IVRS Collection feed file %s.", l_cat1_ivrs_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
sprintf(traceBuf, "CLARITY Collection feed file %s.", l_cat1_clarity_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL WHENEVER NOT FOUND GOTO COL_LABEL1;
for(;;)
{
//---------- Retriving records -----------------
memset( g_service_number.arr, '\0', g_service_number.len );
memset( g_created_dtm.arr, '\0', g_created_dtm.len );
EXEC SQL FETCH :g_col_feed_cursor INTO :g_new_acc_num, :g_new_bill_seq, :g_new_action_num, :g_action_seq, :g_action_id, :g_credit_class, :g_created_dtm, :g_crm_created_dtm, :g_request_status, :g_outstanding_tot, :g_rollup_debt, :g_service_number INDICATOR :g_ind_var, :g_bb_flag;
//------- Storing into string variable for comparison ----
sprintf(l_action_id_str,"%d",g_action_id);
sprintf(l_creclass_id_str,"%d",g_credit_class);
//---------- Else If IVRS Action type then write into IVRS feed file -----
if( tokencheck( (char *)g_ivrs_act_type_info.arr, g_action_id) == 1 )
{
l_fwrite_sts = fprintf (l_cat1_ivrs_fhandle, "%s|%s|%d|%s\n", g_new_acc_num.arr, g_service_number.arr, g_action_id, g_created_dtm.arr);
if ( l_fwrite_sts != NULL)
l_cat1_ivrs_file_rec_cnt++;
}
//---------- Else If its OCB Action type then write into Clarity feed file -----
else if( g_ocb_act_type_info == g_action_id )
{ l_fwrite_sts = fprintf (l_cat1_clarity_fhandle, "%s,%s,OGB,%s,%.0f,%s\n", g_new_acc_num.arr, g_service_number.arr, g_created_dtm.arr, g_outstanding_tot, g_bb_flag.arr);
if ( l_fwrite_sts != NULL)
l_cat1_clarity_file_rec_cnt++;
}
//---------- Else If its ICB Action type then write into Clarity feed file -----
else if( g_icb_act_type_info == g_action_id )
{ l_fwrite_sts = fprintf (l_cat1_clarity_fhandle, "%s,%s,ICB,%s,%.0f,%s\n", g_new_acc_num.arr, g_service_number.arr, g_created_dtm.arr, g_outstanding_tot, g_bb_flag.arr);
if ( l_fwrite_sts != NULL)
l_cat1_clarity_file_rec_cnt++;
}
//---------- Else If its OGICB Action type then write into Clarity feed file -----
else if( g_ogicb_act_type_info == g_action_id )
{ l_fwrite_sts = fprintf (l_cat1_clarity_fhandle, "%s,%s,OGICB,%s,%.0f,%s\n", g_new_acc_num.arr, g_service_number.arr, g_created_dtm.arr, g_outstanding_tot, g_bb_flag.arr);
if ( l_fwrite_sts != NULL)
l_cat1_clarity_file_rec_cnt++;
}
//---------- Else If its DAB Action type then write into Clarity feed file -----
else if( g_dab_act_id == g_action_id )
{ l_fwrite_sts = fprintf (l_cat1_clarity_fhandle, "%s,%s,DAB,%s,%.0f,%s\n", g_new_acc_num.arr, g_service_number.arr, g_created_dtm.arr, g_outstanding_tot, g_bb_flag.arr);
if ( l_fwrite_sts != NULL)
l_cat1_clarity_file_rec_cnt++;
}
//---------- Else If its OGDAB Action type then write into Clarity feed file -----
else if( g_ogdab_act_id == g_action_id )
{ l_fwrite_sts = fprintf (l_cat1_clarity_fhandle, "%s,%s,OGDAB,%s,%.0f,%s\n", g_new_acc_num.arr, g_service_number.arr, g_created_dtm.arr, g_outstanding_tot, g_bb_flag.arr);
if ( l_fwrite_sts != NULL)
l_cat1_clarity_file_rec_cnt++;
}
//---------- Else insert record into CRM feed file -----
/*else
{
l_fwrite_sts = fprintf (l_cat2_fhandle, "%s,%ld,%d,%d,%d,%ld,%s,%.0f,%.0f,%s\n", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_seq, g_action_id, g_credit_class, g_created_dtm.arr, g_outstanding_tot, g_rollup_debt, g_service_number.arr);
if ( l_fwrite_sts != NULL)
l_cat2_file_rec_cnt++;
}
*/
if ( l_fwrite_sts == NULL)
{
sprintf(traceBuf, "Error in writing Collection feed file for record '%s','%ld','%d','%ld','%s','%.0f','%.0f','%s'", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id, g_created_dtm.arr, g_outstanding_tot, g_rollup_debt, g_service_number.arr);
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
:g_error_msg := 'Error writing in Collection feed file for record "'||:g_new_action_num|| '","' ||:g_new_bill_seq || '","'||:g_new_action_num|| '","'||:g_action_seq ||'","'||:g_action_id||'","'||:g_credit_class||'","'||:g_created_dtm|| '","' ||'","'||:g_outstanding_tot|| '","' ||:g_rollup_debt || '","' || :g_service_number ||'"';
BSNL_COLLECTION_PACKAGE.BSNL_PRO_COL_FEEDFILE_ACTION(:g_new_acc_num,:g_new_bill_seq,:g_new_action_num,:g_action_seq,1,:g_error_msg,1);
END;
END-EXEC;
} else
{
EXEC SQL EXECUTE
BEGIN
BSNL_COLLECTION_PACKAGE.BSNL_PRO_COL_FEEDFILE_ACTION(:g_new_acc_num,:g_new_bill_seq,:g_new_action_num,:g_action_seq,NULL,NULL,0);
END;
END-EXEC;
}
} //---- end for loop ----
LOCAL_ERR1:
EXEC SQL CLOSE :g_col_feed_cursor;
EXEC SQL ROLLBACK TO COL_FILE_SAVEPOINT;
strcpy( (char *)g_error_msg.arr, "Error in Collection feed file processing. ");
strcat ((char *)g_error_msg.arr, sqlca.sqlerrm.sqlerrmc);
g_error_msg.len = strlen((char *)g_error_msg.arr);
g_error_code = sqlca.sqlcode;
//------ Deleting all the feed files as these are un-necessary -----
/*if ( remove (l_cat2_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting Collection feed file '%s' for Category II customers. Please delete it manually", l_cat2_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}
*/
if ( remove (l_cat1_ivrs_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting Collection feed file '%s' for IVRS Action types. Please delete it manually", l_cat1_ivrs_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}
if ( remove (l_cat1_clarity_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting Collection feed file '%s' for CLARITY Action types. Please delete it manually", l_cat1_clarity_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ERROR_DATA(BSNL_COLLECTION_PACKAGE.G_SYSDATE,
'BSNL_DEE_COL_FEED_FILE_WRITE_PROCESS',
:g_error_code,
:g_error_msg);
END;
END-EXEC;
COL_LABEL1:
/*fclose(l_cat2_fhandle);*/
fclose(l_cat1_ivrs_fhandle);
fclose(l_cat1_clarity_fhandle);
//fclose(l_crm_dunning_fhandle);
EXEC SQL CLOSE :g_col_feed_cursor;
EXEC SQL COMMIT;
//-------- Deleting Category II feed file, if of zero size ---------
/*if (l_cat2_file_rec_cnt == 0 )
{
if ( remove (l_cat2_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting zero size Category II Collection feed file '%s'. Please delete it manually", l_cat2_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}else
{
sprintf(traceBuf, "Deleted zero size Category II Collection feed file '%s' successfully", l_cat2_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}
*/
//-------- Deleting Category I, CLARITY feed file, if of zero size ---------
if (l_cat1_clarity_file_rec_cnt == 0 )
{
if ( remove (l_cat1_clarity_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting zero size Category I, CLARITY Collection feed file '%s'. Please delete it manually", l_cat1_clarity_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}else
{
sprintf(traceBuf, "Deleted zero size Category I, CLARITY Collection feed file '%s' successfully", l_cat1_clarity_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}
// Updating Clarity reporting table ...
else {
insert_ftp_file ( l_clarity_file_name, l_cat1_clarity_file_rec_cnt, (char *)"CLARITY" );
sprintf( l_clarity_done_file_name, "%s/dunning_%s.done", g_clarity_feedfile_dir.arr, g_timestamp.arr);
l_cat_done_fhandle = fopen(l_clarity_done_file_name,"w+");
fclose(l_cat_done_fhandle);
}
//-------- Deleting Category I, IVRS feed file, if of zero size ---------
if (l_cat1_ivrs_file_rec_cnt == 0 )
{
if ( remove (l_cat1_ivrs_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting zero size Category I, IVRS Collection feed file '%s'. Please delete it manually", l_cat1_ivrs_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}else
{
sprintf(traceBuf, "Deleted zero size Category I, IVRS Collection feed file '%s' successfully", l_cat1_ivrs_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}
return;
}
//-------------- main function starts here ---------------
main (int argc, char *argv[])
{
//--------------- General variable declaration ---------------
char l_database_env[35] ;
//--------------- PMS File operation variables ---------------
char l_local_dtm[15];
char l_file_name[250];
char l_pms_done_file[250];
char l_dest_file_name[200];
char *l_last_dun_accnum;
FILE *l_pms_fhandle;
struct tm *l_local_time;
int l_fwrite_sts;
time_t t;
//--------------- Declaring variables to keep track of old values ---------------
int l_old_action_num = 0;
long l_old_bill_seq = 0;
int l_save_eli_cust_flag = 0;
char *l_old_acc_num;
//--------------- checking trace settings --------------
if ( strcmp( (char *)getenv(TRACE_VAR), (char *)TRC_LEVEL_FULL) == 0)
traceFlag = FLAG_ON;
else
traceFlag = FLAG_OFF;
/*S*****************************************************
* Process start notification.
***************************************************S*/
sprintf(traceBuf, "BSNL_DEE process started...");
traceOutput(__LINE__, INF_LVL, traceBuf);
/*S***********************************************************************************
* Description: Printing command line arguments.
*
***********************************************************************************S*/
int l_char;
while(( l_char = getopt( argc, argv, "p:m:c:t:w:r:k:s:")) != -1)
{
switch(l_char)
{
case 'p':
strcpy(g_instance_id, optarg);
sprintf(traceBuf, "Value for p flag is |%s|", optarg);
traceOutput(__LINE__, INF_LVL, traceBuf);
break;
case 'r':
strcpy(g_instance_id, optarg);
sprintf(traceBuf, "Value for r flag is |%s|", optarg);
traceOutput(__LINE__, INF_LVL, traceBuf);
break;
case ':':
sprintf(traceBuf, "Option -%c requires an operand", optopt );
traceOutput(__LINE__, INF_LVL, traceBuf);
break;
case '?':
sprintf(traceBuf, "Unrecognized option: -%c", optopt );
traceOutput(__LINE__, INF_LVL, traceBuf);
break;
default :
break;
}
if ( atoi(g_instance_id) != 0)
break;
}
sprintf(traceBuf, "Process Instance ID: -%s", g_instance_id );
traceOutput(__LINE__, INF_LVL, traceBuf);
/*S*****************************************************
* Automatic Connection to Oracle.
***************************************************S*/
EXEC SQL WHENEVER SQLERROR GOTO ERR;
EXEC SQL WHENEVER NOT FOUND CONTINUE;
strncpy(l_database_env, "DATABASE", sizeof(l_database_env));
g_connect_str = (char *)getenv(l_database_env);
if(g_connect_str == NULL)
{
sprintf(traceBuf, "Database connection failed no value for $DATABASE...");
traceOutput(__LINE__, ERR_LVL, traceBuf);
g_rec_total++;
g_err_total++;
UpdateProcessInstanceLogInfo(9);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(1);
}
EXEC SQL CONNECT :g_connect_str ;
//------------------------------ Creating first savepoint ----------------------------
EXEC SQL SAVEPOINT SAVEPOINT_MAIN;
/*S***********************************************************************************
* Description: Retrieving Dunning and Non-dunning Action types, PMS Destination file
* and CRM Feed File Destination file location, DEBTage task ID information.
* If error encounters then BSNL_DEE process shall be aborted.
***********************************************************************************S*/
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_GET_CUSTOM_PARAMS_INFO(
P_DUN_ACTION_TYPE => :g_dun_actiontype_str INDICATOR :g_ind_var,
P_SMS_CALL_ACT_TYPE => :g_nondun_actiontype_str INDICATOR :g_ind_var,
P_BSNLDEE_DEBTAGE_ID => :g_DEBTage_task_id INDICATOR :g_ind_var,
P_PMS_FILE_DIR => :g_pms_feedfile_dir_str INDICATOR :g_ind_var,
P_IVRS_FEEDFILE_DIR => :g_ivrs_feedfile_dir INDICATOR :g_ind_var,
P_CLARITY_FEEDFILE_DIR => :g_clarity_feedfile_dir INDICATOR :g_ind_var,
P_IVRS_ACTION_TYPE_INFO => :g_ivrs_act_type_info INDICATOR :g_ind_var,
P_BSNLDEE_OCB_ACTION_ID => :g_ocb_act_type_info INDICATOR :g_ind_var,
P_BSNLDEE_ICB_ACTION_ID => :g_icb_act_type_info INDICATOR :g_ind_var,
P_BSNLDEE_PD_ACTION_ID => :g_pd_act_type_info INDICATOR :g_ind_var,
P_BSNLDEE_OGICB_ACTION_ID => :g_ogicb_act_type_info INDICATOR :g_ind_var,
P_BSNLDEE_BB_PROD_ID => :g_bb_prod_id INDICATOR :g_ind_var,
P_BSNLDEE_BB_BAR_PROD_ID => :g_bb_bar_prod_id INDICATOR :g_ind_var,
P_CRM_DBLINK => :g_crm_dblink INDICATOR :g_ind_var,
P_BSNLDEE_OGB_VIP_ACTION_ID => :g_ogb_vip_act_id INDICATOR :g_ind_var,
P_BSNLDEE_VIP_ACT_INFO => :g_vip_act_type_info INDICATOR :g_ind_var,
P_BSNLDEE_DAB_ACTION_ID => :g_dab_act_id INDICATOR :g_ind_var,
P_BSNLDEE_OGDAB_ACTION_ID => :g_ogdab_act_id INDICATOR :g_ind_var);
IF :g_return_sts = :FAILURE THEN
:g_error_msg := BSNL_COLLECTION_PACKAGE.GLOBAL_ERR;
END IF;
-- Retrieving Global date parameter.
:g_systemdate := to_char(GNVGEN.SYSTEMDATE, 'mm/dd/yyyy');
:g_pmsfeeddate := to_char(GNVGEN.SYSTEMDATE, 'dd-mm-yy');
-- Retrieving TIMESTAMP for feed file creation.
:g_timestamp := TO_CHAR( GNVGEN.systemDate, 'DDMMYYYYHH24MISS'); -- YYYYMMDDHH24MISS
END;
END-EXEC;
if ( g_return_sts == FAILURE )
{
sprintf(traceBuf, "%s. BSNL_DEE process terminated", g_error_msg.arr);
traceOutput(__LINE__, ERR_LVL, traceBuf);
g_rec_total++;
g_err_total++;
EXEC SQL ROLLBACK TO SAVEPOINT SAVEPOINT_MAIN;
UpdateProcessInstanceLogInfo(9);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(1);
}
g_dun_action_ptr = (char *) malloc(sizeof(char) * g_dun_actiontype_str.len+1);
g_nondun_action_ptr = (char *) malloc(sizeof(char) * g_nondun_actiontype_str.len+1);
g_pms_feedfile_dir_ptr = (char *) malloc(sizeof(char) * g_pms_feedfile_dir_str.len+1);
g_vip_act_type_ptr = (char *) malloc(sizeof(char) * g_vip_act_type_info.len+1);
strcpy( g_dun_action_ptr, (char *)g_dun_actiontype_str.arr);
strcpy( g_nondun_action_ptr, (char *)g_nondun_actiontype_str.arr);
strcpy( g_pms_feedfile_dir_ptr, (char *)g_pms_feedfile_dir_str.arr);
strcpy( g_vip_act_type_ptr, (char *)g_vip_act_type_info.arr);
/*S***************************************************************
* Initializing old Dunning Account_num & old Account_num variables
*****************************************************************S*/
l_last_dun_accnum = (char *) malloc(sizeof(char) * 2);
l_old_acc_num = (char *) malloc(sizeof(char) * 2);
strcpy(l_last_dun_accnum, "0");
strcpy(l_old_acc_num, "0");
EXEC SQL WHENEVER SQLERROR GOTO ERR;
sprintf(traceBuf, "After retriving mandatory information successfully");
traceOutput(__LINE__, INF_LVL, traceBuf);
/***********************************************************************************
* Description: Updating BSNL_ACCOUNTATTRIBUTES table with pre IRB DEBT engine run information.
* If any error abort the BSNL_DEE process logging Error.
***********************************************************************************/
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_UPDATE_PRE_PROCESS_DATA;
IF :g_return_sts = :FAILURE THEN
:g_error_msg := BSNL_COLLECTION_PACKAGE.GLOBAL_ERR;
END IF;
END;
END-EXEC;
if ( g_return_sts == FAILURE )
{
sprintf(traceBuf, "%s. BSNL_DEE process terminated...", g_error_msg.arr);
traceOutput(__LINE__, ERR_LVL, traceBuf);
g_rec_total++;
g_err_total++;
EXEC SQL ROLLBACK TO SAVEPOINT SAVEPOINT_MAIN;
UpdateProcessInstanceLogInfo(9);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(1);
}
sprintf(traceBuf, "Successfully updated eligible Accounts data information");
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL COMMIT;
/************************************************************************************
* Description: Updating outstanding information of eligible Accounts by Running IRB debt engine.
* If error encounters then log the error and terminate the process.
************************************************************************************/
sprintf(traceBuf, "Updating IRB DEBT engine information....%ld ", g_DEBTage_task_id);
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.CREATE_DEBTAGE_TASK(:g_DEBTage_task_id);
END;
END-EXEC;
sprintf(traceBuf, "Updated IRB DEBT engine information with return status %d....", g_return_sts);
traceOutput(__LINE__, INF_LVL, traceBuf);
//----------- If IRB DEBT engine aborts then exit logging error now ----------
if ( g_return_sts != 0 && g_return_sts != 1 )
{
sprintf(traceBuf, "Error updating outstanding information by IRB debt engine (DEBTage). Aborting BSNL_DEE process. Please do the corrective action manually");
traceOutput(__LINE__, ERR_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
:g_error_msg := 'Error occured while updating outstanding information by IRB debt engine (DEBTage).';
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ERROR_DATA( BSNL_COLLECTION_PACKAGE.G_SYSDATE,
'BSNL_DEE',
1,
:g_error_msg);
END;
END-EXEC;
g_rec_total++;
g_err_total++;
EXEC SQL ROLLBACK TO SAVEPOINT SAVEPOINT_MAIN;
UpdateProcessInstanceLogInfo(9);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(1);
}
/************************************************************************************
* Description: If IRB DEBT engine finishes with error
* then exclude Errored Accounts from BSNL_DEE processing
*
************************************************************************************/
l_save_eli_cust_flag = 1;
if ( g_return_sts != 0 )
{
sprintf(traceBuf, "Excluding Accounts, for those error in updating outstanding information. Please do the corrective action manually");
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL
UPDATE BSNL_ACCOUNTATTRIBUTES BAA
SET BAA.BSNLDEE_EXCLUDE_FLAG = 1
WHERE BAA.ACCOUNT_NUM IN (SELECT A.ACCOUNT_NUM FROM ACCOUNT A
WHERE A.Debt_Analysis_Required_Dtm <= to_date(:g_systemdate, 'mm/dd/yyyy'));
}
EXEC SQL COMMIT;
//----------------- Creating second Savepoint ---------------------------------------
EXEC SQL SAVEPOINT SAVE_ELI_CUST_INFO;
/************************************************************************************
* Description: Retrieving Eligible Collection Data into cursor.
* If error encounters then BSNL_DEE process shall be aborted.
************************************************************************************/
EXEC SQL ALLOCATE :g_eli_acc_cursor;
strcpy( (char *)g_error_msg.arr, "Error retriving eligible customer data.");
EXEC SQL EXECUTE
BEGIN
BSNL_COLLECTION_PACKAGE.BSNL_GET_COL_DATA ( :g_eli_acc_cursor );
END;
END-EXEC;
EXEC SQL WHENEVER SQLERROR GOTO ERR;
/***********************************************************************************
* Description: Generates PMS dunning file name dynamically & opens it if any error
* abort the BSNL_DEE process logging Error.
***********************************************************************************/
t = time(NULL);
l_local_time = localtime(&t);
memset( l_file_name, '\0', sizeof(l_file_name) );
sprintf( TIMESTAMP, "%02d%02d%02d%02d%02d%02d", l_local_time->tm_year+1900, l_local_time->tm_mon+1, l_local_time->tm_mday, l_local_time->tm_hour, l_local_time->tm_min, l_local_time->tm_sec);
/*sprintf( l_file_name, "%s/dunning_%s.csv", g_pms_feedfile_dir_ptr, TIMESTAMP);
//sprintf( l_file_name, "pms_col_dunning_info_%s.txt", TIMESTAMP);
l_pms_fhandle = fopen(l_file_name,"w+");
sprintf(traceBuf, "PMS dunning feed file %s created.", l_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
if ( l_pms_fhandle == NULL)
{
strcpy( (char *)g_error_msg.arr, "Error opening PMS DUNNING feed file.");
g_error_code = 1;
sprintf(traceBuf, "%s Error is %s.", g_error_msg.arr, strerror(errno) );
traceOutput(__LINE__, ERR_LVL, traceBuf);
goto ERR;
}
*/
EXEC SQL WHENEVER NOT FOUND GOTO FINISH;
/*S***********************************************************************************
* Description: Retrieving data from cursor record by record.
* Checks type of Action for this record.
* If Action type SMS or IVRS Call retrieve Contact Detail, if found then
* insert record else log error.
* If Action type some Dunning Action retrieve all Event sourcess, if found then
* insert for each each event source record else log error.
* If some other Action type then insert Action record else log error.
* Update BSNL_ACCOUNTATTRIBUTES table with last processed record information
* along with Dunning status information, and last time BSNL_DEE process
* run information for each Account.
* Insert Dunning information into PMS feed file.
* Error for each record shall be track seperatly and if any record goes into
* error then log it seperatly and continue processing other records.
* If any error encounters in BSNL_DEE process shall be aborted revoking full transaction.
*
***********************************************************************************S*/
for (;;)
{
//-- Resetting memory for all variable length variables ---
memset( g_new_acc_num.arr, '\0', g_new_acc_num.len );
memset( g_error_msg.arr, '\0', g_error_msg.len );
//l_action_id = g_action_id;
//--------------- Fetching Cursor data record by record ---------------
exec sql fetch :g_eli_acc_cursor INTO :g_new_acc_num, :g_new_bill_seq, :g_new_action_num, :g_action_id, :g_threshold_mny, :g_debt_age, :g_rollup_debt, :g_outstanding_tot, :g_credit_class;
//--------------- Assigning New Action Id to temp varible for comparison ---------------
sprintf(g_action_id_str,"%d",g_action_id);
//-------------- If this is a new Account then log information related to previous Account --------
if ( strcmp( l_old_acc_num, (char *)g_new_acc_num.arr ) != 0 )
{
//--------- If this is not first Account to be processed then update BSNL_ACCOUNTATTRIBUTES table --------
if ( strcmp (l_old_acc_num, (char *)"0") != 0 )
{
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ACC_ATT_DATA(
:l_old_acc_num,
:l_old_bill_seq,
:l_old_action_num,
:g_threshold_mny,
:l_action_id);
COMMIT;
END;
END-EXEC;
}
l_action_id = g_action_id;
l_old_action_num = g_new_action_num;
l_old_bill_seq = g_new_bill_seq;
l_old_acc_num = (char *) malloc(sizeof(char) * g_new_acc_num.len+1);
strcpy(l_old_acc_num,(char *)g_new_acc_num.arr);
}
//------------------ Creating new SAVEPOINT ---------------
EXEC SQL SAVEPOINT ROLLBACK_THIS_REC;
//--------------- If Old & New Bill Seq equals then insert this Action ---------------
if ( l_old_bill_seq == g_new_bill_seq )
{
l_action_id = g_action_id;
l_old_action_num = g_new_action_num;
l_old_bill_seq = g_new_bill_seq;
l_old_acc_num = (char *) malloc(sizeof(char) * g_new_acc_num.len+1);
strcpy(l_old_acc_num,(char *)g_new_acc_num.arr);
//--------------- If Action type 'SMS' or 'IVRS Call' then use following logic ---------------
if ( tokencheck( g_nondun_action_ptr, g_action_id) == 1 )
{
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_PROCESS_SOFT_ACTION(
:g_new_acc_num,
:g_new_bill_seq,
:g_new_action_num,
:g_action_id,
:g_outstanding_tot,
:g_rollup_debt,
:g_credit_class,
:g_bb_prod_id,
:g_rec_total,
:g_err_total,
:g_suc_total);
END;
END-EXEC;
//------ If Error then rollback updates for this record -----
if ( g_return_sts == FAILURE )
{
sprintf(traceBuf, "Error processing Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL ROLLBACK TO SAVEPOINT ROLLBACK_THIS_REC;
}
//if ( ( g_return_sts != SUCCESS) && ( g_return_sts != NOT_ELIG ))
if ( g_return_sts == SUCCESS )
{
if (traceFlag == FLAG_ON)
{
sprintf(traceBuf, "Successfully processed Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}
}
//--------------- If Action type 'OGB_VIP' or 'ICB_VIP' then use following logic ---------------
if ( tokencheck( g_vip_act_type_ptr, g_action_id) == 1 )
{
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_PROCESS_VIP_ACTION(
:g_new_acc_num,
:g_new_bill_seq,
:g_new_action_num,
:g_action_id,
:g_outstanding_tot,
:g_rollup_debt,
:g_credit_class,
:g_ogb_vip_act_id,
:g_bb_prod_id,
:g_rec_total,
:g_err_total,
:g_suc_total);
END;
END-EXEC;
//------ If Error then rollback updates for this record -----
if ( g_return_sts == FAILURE )
{
sprintf(traceBuf, "Error processing VIP Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL ROLLBACK TO SAVEPOINT ROLLBACK_THIS_REC;
}
//if ( ( g_return_sts != SUCCESS) && ( g_return_sts != NOT_ELIG ))
if ( g_return_sts == SUCCESS )
{
if (traceFlag == FLAG_ON)
{
sprintf(traceBuf, "Successfully processed VIP Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}
}
// --------- If action type some Dunning Action type --------------
if ( tokencheck( g_dun_action_ptr, g_action_id) == 1 )
{
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_PROCESS_EVENT_ACTION(
:g_new_acc_num,
:g_new_bill_seq,
:g_new_action_num,
:g_action_id,
:g_outstanding_tot,
:g_rollup_debt,
:g_credit_class,
:g_bb_prod_id,
:g_bb_bar_prod_id,
:g_pd_act_type_info,
:g_rec_total,
:g_err_total,
:g_suc_total);
END;
END-EXEC;
if ( g_return_sts == SUCCESS )
{
//-------- Check if this Account Name already have entry in PMS Dunning file ------
if ( strcmp( l_last_dun_accnum, (char *)g_new_acc_num.arr ) != 0 )
{
l_last_dun_accnum = (char *) malloc(sizeof(char) * g_new_acc_num.len+1);
strcpy(l_last_dun_accnum,(char *)g_new_acc_num.arr);
if ( g_action_id != g_pd_act_type_info ) {
EXEC SQL EXECUTE
BEGIN
:g_return_sts :=BSNL_COLLECTION_PACKAGE.BSNL_INSERT_PMS_FEED_DATA(:g_new_acc_num,
:g_threshold_mny/1000,
:g_pmsfeeddate);
IF :g_return_sts = :FAILURE THEN
:g_error_msg := BSNL_COLLECTION_PACKAGE.GLOBAL_ERR;
END IF;
END;
END-EXEC;
if ( g_return_sts != SUCCESS)
{
sprintf(traceBuf, "%s", g_error_msg.arr);
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL ROLLBACK TO SAVEPOINT ROLLBACK_THIS_REC;
continue;
}
/*
l_fwrite_sts = fprintf (l_pms_fhandle,"%s,%.0f,%s\n",g_new_acc_num.arr, g_threshold_mny/1000, g_pmsfeeddate.arr);
if ( l_fwrite_sts != NULL)
{
g_file_rec_cnt++;
}else
{
sprintf(traceBuf, "Error in writing in PMS file for Account %s with threshold money %.0f and processing date %s.", g_new_acc_num.arr, g_threshold_mny, g_pmsfeeddate.arr);
traceOutput(__LINE__, WRN_LVL, traceBuf);
sprintf(traceBuf, "Error processing Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL EXECUTE
BEGIN
:g_error_msg := 'Error writing in PMS_DUNNING file for Account "'||:g_new_action_num|| '" with threshold money "' ||:g_threshold_mny || '" and processing date "' || :g_pmsfeeddate || '".';
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ERROR_DATA(
BSNL_COLLECTION_PACKAGE.G_SYSDATE,
'BSNL_DEE',
:FAILURE,
:g_error_msg);
END;
END-EXEC;
EXEC SQL ROLLBACK TO SAVEPOINT ROLLBACK_THIS_REC;
}
*/
}
}//else
//{
if (traceFlag == FLAG_ON)
{
sprintf(traceBuf, "Successfully processed Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
//}
}
//if ( ( g_return_sts != SUCCESS) && ( g_return_sts != NOT_ELIG ))
if ( g_return_sts == FAILURE )
{
sprintf(traceBuf, "Error processing Account %s,for Bill Sequence %ld, Action Number %d, with Action ID %d", g_new_acc_num.arr, g_new_bill_seq, g_new_action_num, g_action_id);
traceOutput(__LINE__, WRN_LVL, traceBuf);
EXEC SQL ROLLBACK TO SAVEPOINT ROLLBACK_THIS_REC;
}
}
}//-------------- Logic for Same Bill for this Account ends here ---------------
} //----------------------- end loop for eligible Account records processing ------------------
FINISH:
/*fclose(l_pms_fhandle); // SS
if (g_file_rec_cnt == 0 )
{
if ( remove (l_file_name) == -1 )
{
sprintf(traceBuf, "Error deleting zero size PMS file '%s'", l_file_name);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}else
{
sprintf(traceBuf, "Deleted zero size PMS file '%s' successfully", l_file_name);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}else {
memset( l_pms_done_file, '\0', sizeof(l_pms_done_file) );
sprintf( l_pms_done_file, "%s/dunning_%s.done", g_pms_feedfile_dir_ptr, TIMESTAMP);
l_pms_fhandle = fopen(l_pms_done_file,"w+");
sprintf(traceBuf, "Created zero size pms .done file %s", l_pms_done_file);
traceOutput(__LINE__, INF_LVL, traceBuf);
fclose(l_pms_fhandle);
}
*/
sprintf(traceBuf, "Finally Total records %ld, Success records %ld, Errored records %ld...", g_rec_total, g_suc_total, g_err_total);
traceOutput(__LINE__, INF_LVL, traceBuf);
//------------- Finally inserting details for Last processed Account -------------
if ( strcmp (l_old_acc_num, (char *)"0") != 0 )
{
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ACC_ATT_DATA(
:l_old_acc_num,
:l_old_bill_seq,
:l_old_action_num,
:g_threshold_mny,
:l_action_id);
COMMIT;
END;
END-EXEC;
}
/*********************************************************************************
* Description: If file is of zero size then remove it
* Else, retrive the value of PMS file destination location.
* Move the PMS file to the desired location. If error then log it.
*********************************************************************************/
//Closing the cursor and committing everything till now
EXEC SQL CLOSE :g_eli_acc_cursor;
EXEC SQL COMMIT;
EXEC SQL EXECUTE
BEGIN
BSNL_COLLECTION_PACKAGE.BSNL_SUPRESS_ACTION( P_ERROR_CODE => :g_error_code INDICATOR :g_ind_var,
P_ERROR_MESSAGE => :g_error_msg INDICATOR :g_ind_var);
END;
END-EXEC;
CreateFeedFile(); //To be un-commented once to make automatic
EXEC SQL EXECUTE
BEGIN
:g_return_sts :=BSNL_COLLECTION_PACKAGE.BSNL_CREATE_PMS_FILE( P_PMS_FILE_DIR => :g_pms_feedfile_dir_str,
P_PMS_FEED_FILE => :g_pms_feed_filename INDICATOR :g_ind_var);
IF :g_return_sts = :FAILURE THEN
:g_error_msg:= BSNL_COLLECTION_PACKAGE.GLOBAL_ERR;
END IF;
END;
END-EXEC;
if ( g_return_sts == 0)
{
if ( strcmp( "0", (char *)g_pms_feed_filename.arr ) != 0 )
{
sprintf(traceBuf, "PMS feed file created at %s,with file Name %s", g_pms_feedfile_dir_str.arr, g_pms_feed_filename.arr);
traceOutput(__LINE__, INF_LVL, traceBuf);
}
}
else
{
sprintf(traceBuf, "%s", g_error_msg.arr);
traceOutput(__LINE__, WRN_LVL, traceBuf);
}
if ( g_err_total != 0)
UpdateProcessInstanceLogInfo(8);
else
UpdateProcessInstanceLogInfo(7);
//--------------- Process finish notification ---------------
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
sprintf(traceBuf, "Finished BSNL_DEE process");
traceOutput(__LINE__, INF_LVL, traceBuf);
exit(0);
//If error then insert into BSNL_DEBT_ERROR_LOG table...
ERR:
strcat ((char *)g_error_msg.arr, sqlca.sqlerrm.sqlerrmc);
g_error_msg.len = strlen((char *)g_error_msg.arr);
if (g_error_code != 1)
g_error_code = sqlca.sqlcode;
sprintf(traceBuf, "Process finished with error %s", g_error_msg.arr);
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL CLOSE :g_eli_acc_cursor;
//----- Following to avoid the infinite loop in case ROLLBACK fails -----
EXEC SQL EXECUTE
BEGIN
:g_return_sts := BSNL_COLLECTION_PACKAGE.BSNL_INSERT_ERROR_DATA(BSNL_COLLECTION_PACKAGE.G_SYSDATE,
'BSNL_DEE',
:g_error_code,
:g_error_msg);
END;
END-EXEC;
if (g_err_total == 0)
{
g_rec_total++;
g_err_total++;
}
if ( l_save_eli_cust_flag == 1 )
EXEC SQL ROLLBACK TO SAVEPOINT SAVE_ELI_CUST_INFO;
else
EXEC SQL ROLLBACK TO SAVEPOINT SAVEPOINT_MAIN;
UpdateProcessInstanceLogInfo(9);
//--------------- Process finish notification ---------------
sprintf(traceBuf, "Finished BSNL_DEE process with errors");
traceOutput(__LINE__, INF_LVL, traceBuf);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;
exit(1);
}
/*F************************************************************************
*
* FUNCTION : traceOutput
*
* PARAMETERS : lineNo - This is the source code line number.
* funcName - Name of the function for which this trace has
* to be written.
* level - trace level. ERR_LVL and WRN_LVL will always
* be written.
* msg - Message to be displayed on STDOUT.
*
* RETURNS : Nothing
*
* Description: This function is used to write error messages in standard
* Convergys format to stderr.
*
***************************************************************************/
void traceOutput(int lineNo,
int level,
const char *msg )
{
char l_local_dtm[DATETIMELEN+1];
struct tm *l_local_time;
time_t t;
memset( l_local_dtm, '\0', sizeof(l_local_dtm) );
t = time(NULL);
l_local_time = localtime(&t);
sprintf( l_local_dtm, "%02d/%02d/%02d %02d:%02d:%02d", l_local_time->tm_mday, l_local_time->tm_mon+1,
l_local_time->tm_year-100, l_local_time->tm_hour,
l_local_time->tm_min, l_local_time->tm_sec );
if (level == TRC_LVL)
{
fprintf( stderr, "TRACE-----%s:%d:%s > %s\n", __FILE__, lineNo, l_local_dtm, msg );
}
else if (level == ERR_LVL )
{
fprintf( stderr, "ERROR-----%s:%d:%s > %s\n", __FILE__, lineNo, l_local_dtm, msg );
}
else if (level == WRN_LVL )
{
fprintf( stderr, "WARN------%s:%d:%s > %s\n", __FILE__, lineNo, l_local_dtm, msg );
}
else if (level == INF_LVL)
{
fprintf( stderr, "INFORM----%s:%d:%s > %s\n", __FILE__, lineNo, l_local_dtm, msg );
}
}/* end of traceOutput() */
.png)
.png)
No comments:
Post a Comment